| | |
|
|
|
Re: Writing a query (probably using SQL) |
|---|
| message from Dirk Goldgar on 5 May 2004 |
"Pieter Wijnen" <mysurnameonly@online.no> wrote in message
news:IQ_lc.153429$jf4.8202376@news000.worldonline.dk
The SQL IIf() function does recognize "Is Null". The VBA IIF() function
doesn't. So you can use "IIf([SomeField] Is Null, ...)" in a query, but
not in VBA code.
|
| tina replied to Dirk Goldgar on 05 May 2004 |
okay, that explains it. i wondered if i was hallucinating. btw, when you
mention the IIf() function in VBA, are you talking about the
If...Then...Else statement? or can you use the IIf() function directly in
VBA? in an Eval() function, maybe? (there's still lots, and lots more, i
don't know about VBA.)
"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:evvDRJsMEHA.892@TK2MSFTNGP09.phx.gbl...
|
| Dirk Goldgar replied to tina on 5 May 2004 |
"tina" <nospam@address.com> wrote in message
news:whamc.22333$Ut1.659454@bgtnsc05-news.ops.worldnet.att.net
No, I'm not talking about the "If ... Then ... Else" statements. There
is a VBA function named IIf(), and there is a builtin function in Jet
SQL named IIf(). They are almost, but not quite, identical. The main
differences are:
1. The SQL function understands "Is Null"; the VBA function doesn't.
2. The VBA function always evaluates both alternative result expressions
(which can raise an error if, for example, one of the expressions
divides by zero -- even if that result is not returned), while the SQL
function only evaluates the result expression that will be returned.
You can use the VBA IIf() function directly in VBA, though I always
prefer a regular If ... Then ... Else construct, as it's more efficient
and not subject to the problem noted in item 2, above. The difference
between the two functions is obscured because VBA functions can be used
in Access queries, but when you use IIf() in a Jet query it's the Jet
SQL function that is called.
|
|
Archived message: Re: Writing a query (probably using SQL) (MS Access)
