Unable to Use Nz Function!

[scharf]

I created a new field in my query using expert advise from this group:

Task_Date: IIf(Nz([TaskDate],"1/1/1900")="1/1/1900","",[TaskDate])

When I run the query it returns exactly what I am looking for, but when I
return to the excel sheet and try to refresh the pivot it tells me:

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

Can anyone help me?

 

[Dirk Goldgar]

I created a new field in my query using expert advise from this group:

Task_Date: IIf(Nz([TaskDate],"1/1/1900")="1/1/1900","",[TaskDate])

When I run the query it returns exactly what I am looking for, but
when I return to the excel sheet and try to refresh the pivot it
tells me:

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

When you run the query from Access, the Access Expression Service
resolves the call to the VBA function. That service isn't available
when the query is run from Excel.

Hmm ... Try this version of the field definition and see if it works
from Excel:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = "1/1/1900",
"",[TaskDate])

Note: I had to break that onto two lines to post it, but actually it
would all be on one line.

 

[scharf]

I tried that and the query returns <#Error> for the entire column. I am
using Excel 2000.

I created a new field in my query using expert advise from this group:

Task_Date: IIf(Nz([TaskDate],"1/1/1900")="1/1/1900","",[TaskDate])

When I run the query it returns exactly what I am looking for, but
when I return to the excel sheet and try to refresh the pivot it
tells me:

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

When you run the query from Access, the Access Expression Service
resolves the call to the VBA function. That service isn't available
when the query is run from Excel.

Hmm ... Try this version of the field definition and see if it works
from Excel:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = "1/1/1900",
"",[TaskDate])

Note: I had to break that onto two lines to post it, but actually it
would all be on one line.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I tried that and the query returns <#Error> for the entire column. I
am using Excel 2000.

Hmm ... Try this version of the field definition and see if it works
from Excel:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = "1/1/1900",
"",[TaskDate])

Is TaskDate a Text field or a Date/Time field? The expression you
posted implied it was a text field, and if it were my suggestion ought
to have worked. If it's a Date field, though, that expression will give
a type mismatch error. In that case, try this:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = #1/1/1900#,
Null,[TaskDate])

 

[scharf]

It worked! Thank you so much for your expertise.

I tried that and the query returns <#Error> for the entire column. I
am using Excel 2000.

Hmm ... Try this version of the field definition and see if it works
from Excel:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = "1/1/1900",
"",[TaskDate])

Is TaskDate a Text field or a Date/Time field? The expression you
posted implied it was a text field, and if it were my suggestion ought
to have worked. If it's a Date field, though, that expression will give
a type mismatch error. In that case, try this:

Task_Date: IIf([TaskDate] Is Null Or [TaskDate] = #1/1/1900#,
Null,[TaskDate])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)