Nz function not recognized in ODBC Access Driver

[sm43]

Hi,
I'm using the Nz function in an Access query that I'm executing from ASP. I get:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression

at the line where the query is executed in ASP. The query is running fine within Access.

Here is my code:


SQL = "select Organization, Sum(Nz(Credit)+Nz(q1*p1)+Nz(q2*p2)+Nz(q3*p3)+ Nz(q4*p4)+Nz(q5*p5)+Nz(q6*p6)+Nz(q7*p7)+Nz(q8*p8)+Nz(Debit)) As balance from Invoice where Invoice.OrgID = OrganizationAddresses.OrgID And Invoice.InvoiceNumber = EventScheduling.TransNumber Group By Organization, balance"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.Open SQL, MyConn


It doesn't matter here whether I put this query in ASP or put it in Access and execute it by name.

Appreciate in-advance any help with this question.

Thanks.

Saad

 

[Falcon99]

You'll need to trap for Nulls before building the string.


You might try this. This is MS's workaround for Dataasccess pages.

MSODSC.Nz([NumericValueHere],0)

Reference:

http://support.microsoft.com/default.aspx?scid=kb;en-us;274379

HTH
Falcon

 

[cmmrfrds]

VB Scripting does not support the Nz function. You can check for nulls with the iif function.

iif(Credit is null,0,Credit)+iif((q1*p1)is null,0,(q1*p1))+