can Nz() be used in VBA?

[Ken]

I have this in an SQL where clause. I'm getting an error, and I don't see
it.
((" & Chr(34) & Nz([orders].[Paragraph], "") & Chr(34) & ")=" & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & ")

I've never used Nz before, can it be used or do I have to evaluate the
Orders.Paragraph with an If before the SQL is evaluated?

 

[Brendan Reynolds]

You can use NZ in SQL statements executed by VBA code within Access. You
can't use it if you execute the query from outside Access, e.g. if you tried
to use your code from another VBA-enabled app such as Excel or Word.

What error are you getting?

 

[Ken]

Sorry to all, I didn't do enough research, I found the answer in the post
from 8/29/2005 "Access conversion problem"

 

[Ken]

The Sql is evaluated by vba, because it is used in building a string to be
used in retreving a recordset. I tried using IIf, but I got :
"Access can't find the field "|"referred to in your expression"
after reading your post I threw the Nz back in and got the same error. I
don't remember if that is the error I got the last time, so I have to really
look at it to make sure I didn't mess something up.

You can use NZ in SQL statements executed by VBA code within Access. You
can't use it if you execute the query from outside Access, e.g. if you tried
to use your code from another VBA-enabled app such as Excel or Word.

What error are you getting?

--
Brendan Reynolds
Access MVP

I have this in an SQL where clause. I'm getting an error, and I don't
see
it.
((" & Chr(34) & Nz([orders].[Paragraph], "") & Chr(34) & ")=" & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & ")

I've never used Nz before, can it be used or do I have to evaluate the
Orders.Paragraph with an If before the SQL is evaluated?

 

[Ken]

Got It,
(( Nz([orders].[Paragraph], """"))=" & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & ")
I figured by the error that vba was trying to evaluate a field that was
needed in the SQL statement. Hopefully this wasn't a waste of discussion
board resources.

You can use NZ in SQL statements executed by VBA code within Access. You
can't use it if you execute the query from outside Access, e.g. if you tried
to use your code from another VBA-enabled app such as Excel or Word.

What error are you getting?

--
Brendan Reynolds
Access MVP

I have this in an SQL where clause. I'm getting an error, and I don't
see
it.
((" & Chr(34) & Nz([orders].[Paragraph], "") & Chr(34) & ")=" & Chr(34) &
Nz([Forms]![OrdersUnbound]![Paragraph], "") & Chr(34) & ")

I've never used Nz before, can it be used or do I have to evaluate the
Orders.Paragraph with an If before the SQL is evaluated?