0

Background: migrating legendary Excel Reporting projects into MS Access for the very need of database structure.

Limitation: Development environment is within Excel and Access. Due to large chunks of data processing and keeping of moving n-Months data, it can't afford to loop via recordsets.

Issue: In current Excel reporting platform, there are multi number of sub tools are used separately to process data. As a result ithas the priviledge to process small chunks of data by going through each row. Conditional checks are performed using IF-ELSE.

In proposed MS Access structure, these IF-ELSE are converted into IIF. Given theis situation, would like to know opinions on performance comparison of IIF vs SWITCH. Any better solutions are most welcome.

PS: after importing source, the db is auto closed to compact and repair as it "bloats". eventually db should be compatible for both ms 2003 and 2010 packages

bonCodigo
  • 14,268
  • 1
  • 48
  • 91

2 Answers2

1

I think that may be here is what you want:

Microsoft

The Iif function returns one of two values depending on whether the expression is true or not. The following expression uses the Iif function to return a Boolean value of True if the value of LineTotal exceeds 100. Otherwise it returns False.

The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true.

GianlucaBobbio
  • 174
  • 7
  • Thanks GianlucaBobbie! Writing from mobile I didn't add the references. Your answer is suitable for question title, however I am also keen to hear other solutions that could improve the current design. +1 – bonCodigo Aug 14 '13 at 13:15
1

I think that Switch is a VBA function (like Nz) while Iif is an SQL one. That has a lot of implications that you can explore here, on Allen Browne's site.
Those VBA function should specially be avoided in queries when working in a client-server architecture (SQL Server/Oracle or similar backend).

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Very interesting addendum, I'm probably guilty of doing that in some queries. But are you sure it makes a difference? If a query on a linked ODBC table uses `IIf()`, does Access or the ODBC driver translate that into something that can be evaluated server-side? – Andre Apr 11 '17 at 17:06
  • @Andre I am pretty sure `Iif` is correctly translated by ODBC, while Nz is not. Quite easy to test if you have a SQL Server with at least one large table + 1 other table that can be linked to it. Using – iDevlop Apr 11 '17 at 19:46