NZ Function Not Support via ODBC

[MX1]

Help. I have a complex Access database that I've tied into using ASP.
Things work great, except I can't seem to have queries that originate in ASP
use the NZ function. Need it in case there are nulls. It would be a
nightmare to rewrite the queries. Anyone know of an alternate way to
connect to the database so my NZ calcs would be supported?

Many thanks!

[Terry Kreft]

This will not work no matter how you connect.

The Nz function works in queries within Access because the expression
service intercepts it and makes the call to the function.

Using ODBC/ADO/RDO the Access expression service is out of the picture and
so you will get errors.

Have a look at seeing if simply appending an empty string to your
expressions and removing the NZ function sorts your problems out.
--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message news:2I6Cc.91433$eu.44859@attbi_s02...

Help. I have a complex Access database that I've tied into using ASP.
Things work great, except I can't seem to have queries that originate in ASP use the NZ function. Need it in case there are nulls. It would be a
nightmare to rewrite the queries. Anyone know of an alternate way to
connect to the database so my NZ calcs would be supported?

Many thanks!

[MX1]

Thanks Terry. What do you mean by appending an emptry string to the
expressions. Not sure I follow you. Can you provide a brief example?
Thanks!
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:mB********************@karoo.co.uk...

This will not work no matter how you connect.

The Nz function works in queries within Access because the expression
service intercepts it and makes the call to the function.

Using ODBC/ADO/RDO the Access expression service is out of the picture and
so you will get errors.

Have a look at seeing if simply appending an empty string to your
expressions and removing the NZ function sorts your problems out.
--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message

news:2I6Cc.91433$eu.44859@attbi_s02...

Help. I have a complex Access database that I've tied into using ASP.
Things work great, except I can't seem to have queries that originate in

ASP

use the NZ function. Need it in case there are nulls. It would be a
nightmare to rewrite the queries. Anyone know of an alternate way to
connect to the database so my NZ calcs would be supported?

Many thanks!

[Terry Kreft]

Say you had

SELECT NZ([City],"") AS Expr1
FROM Table1;
You could change this to
SELECT [City] & "" AS Expr1
FROM Table1;

--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message news:OOdCc.92454$eu.30902@attbi_s02...

Thanks Terry. What do you mean by appending an emptry string to the
expressions. Not sure I follow you. Can you provide a brief example?
Thanks!
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:mB********************@karoo.co.uk...

This will not work no matter how you connect.

The Nz function works in queries within Access because the expression
service intercepts it and makes the call to the function.

Using ODBC/ADO/RDO the Access expression service is out of the picture and

so you will get errors.

Have a look at seeing if simply appending an empty string to your
expressions and removing the NZ function sorts your problems out.
--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message

news:2I6Cc.91433$eu.44859@attbi_s02... Help. I have a complex Access database that I've tied into using ASP.
Things work great, except I can't seem to have queries that originate

in ASP

use the NZ function. Need it in case there are nulls. It would be a
nightmare to rewrite the queries. Anyone know of an alternate way to
connect to the database so my NZ calcs would be supported?

Many thanks!

[MX1]

Cool. I'll try that. Thanks.

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:be********************@karoo.co.uk...

Say you had

SELECT NZ([City],"") AS Expr1
FROM Table1;
You could change this to
SELECT [City] & "" AS Expr1
FROM Table1;

--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message news:OOdCc.92454$eu.30902@attbi_s02...

Thanks Terry. What do you mean by appending an emptry string to the
expressions. Not sure I follow you. Can you provide a brief example?
Thanks!
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:mB********************@karoo.co.uk...

This will not work no matter how you connect.

The Nz function works in queries within Access because the expression
service intercepts it and makes the call to the function.

Using ODBC/ADO/RDO the Access expression service is out of the picture and so you will get errors.

Have a look at seeing if simply appending an empty string to your
expressions and removing the NZ function sorts your problems out.
--
Terry Kreft
MVP Microsoft Access
"MX1" <mx*@mx1.abc> wrote in message

news:2I6Cc.91433$eu.44859@attbi_s02...

> Help. I have a complex Access database that I've tied into using ASP. > Things work great, except I can't seem to have queries that originate in

ASP
> use the NZ function. Need it in case there are nulls. It would be

a > nightmare to rewrite the queries. Anyone know of an alternate way to > connect to the database so my NZ calcs would be supported?
>
> Many thanks!
>
>