Posted by Craig Kelly on 07/22/05 02:54

"Markon" wrote:

> Hello
> I have recently decided to upgrade my programs to enable users to have
> mssql
> databases instead of access.
> I have since then run into many incompatibilities between their sql:
> access has IIF(x>y,a,b) whereas mssql hase case when (this already means
> hundreds of changes in queries)
> it does not have format(number,'#,##0.00') or format(date,'dd.MM.yyyy')
> but
> most surprising is following:
>
> access allows this while mssql reports error ("Cannot perform an aggregate
> function on an expression containing an aggregate or a subquery")
> SELECT ....
> SUM(a*b/(SELECT SUM(c) FROM d WHERE e=f))
> FROM ...
> (a-f are fields and tables )
>
> I am totaly dissapointed in MS since I will have to have two variants of
> queries in programs just to enable users to choose between databases.
> Does anyone know an MS e-mail where I could flame them

Just so you know... IIf and Format are in Access because the JET database
engine allows you to use native VBA functions. And if you're actually using
Access (instead of just the JET engine via VB, C++, etc), you can also use
VBA functions that you've created.

The problem you're facing is that Access/JET is the only database system
(that I know of) that allows you to use VBA that way. The fact is, if you
want to continue to use them, you'll need to stay with Access (instead of
Oracle, Sybase, SQL Server, DB2, whatever). Your only options would be to
rewrite your SQL (as you mentioned) or implement these functions in your
target environment.

Craig

[Back to original message]