Union Nulls. Why is 4/14/2004 less than 4/2/2004?

[Salad]

A97.

Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:

Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)

The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3

Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many times
that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.

If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the culprit.

This problem is common enough in Google...lots of people recommend using
CDate which is useless. Formatting is worthless. And unless you
actually run a similar query to see the results, you wouldn't believe
Access would be this confused.

The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.

Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)

IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.

If you have an explanation for Query1's results I'd appreciate it. I
suppose that the Dates are cast as Variants when it is run.

Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but that
returned 11/30/1999. Go figure.

[MGFoster]

Salad wrote:

A97.

Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:

Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)

The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3

Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many times
that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.

If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the culprit.

This problem is common enough in Google...lots of people recommend using
CDate which is useless. Formatting is worthless. And unless you
actually run a similar query to see the results, you wouldn't believe
Access would be this confused.

The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.

Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)

IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.

If you have an explanation for Query1's results I'd appreciate it. I
suppose that the Dates are cast as Variants when it is run.

Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but that
returned 11/30/1999. Go figure.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Strange. It looks like the date column is being turned into a string
data type. Perhaps if you put #12/30/1899# instead of NULL the date
column will remain a date column. But, then you'll have to change your
criteria to

WHERE DF <> #12/30/1899# AND DF < Date()

instead of

Where DF < Date()

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
=YYXS
-----END PGP SIGNATURE-----

[Steve Jorgensen]

The problem is that when JET can't determine a single data type for a column,
it automatically converts it to a string. Null does not indicate a data type,
so Null as DF becomes a string. Subsequently, the union of DF becomes a
string as well.

The solution? There is an undocumented form if IIf that you can use in
queries with only 2 arguments. JET is smart enough to make the column use the
data type indicated by argment number 2, even though IIf returns Null when
argument 1 is False.

So, the reformulatd union query looks like this...

SELECT
Text1 As TF,
Date1 As DF
FROM Table1
UNION ALL SELECT
Text2 As TF,
Date2 As DF
FROM Table2
UNION ALL SELECT
Text3 As TF,
IIf(False,#1/1/2000#) As DF
FROM Table3
IIf(False,#1/1/2000#) generates a date column with all Null values. You can
do the same thing with other data types, e.g.
IIf(False,CLng(0))
IIf(False,CCur(0))

On Sat, 03 Apr 2004 06:13:33 GMT, Salad <oi*@vinegar.com> wrote:

A97.

Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:

Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)

The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3

Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many times
that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.

If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the culprit.

This problem is common enough in Google...lots of people recommend using
CDate which is useless. Formatting is worthless. And unless you
actually run a similar query to see the results, you wouldn't believe
Access would be this confused.

The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.

Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)

IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.

If you have an explanation for Query1's results I'd appreciate it. I
suppose that the Dates are cast as Variants when it is run.

Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but that
returned 11/30/1999. Go figure.

[Salad]

MGFoster wrote:

Salad wrote:

A97.

Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:

Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)

The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3

Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many
times that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.

If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the
culprit.

This problem is common enough in Google...lots of people recommend
using CDate which is useless. Formatting is worthless. And unless
you actually run a similar query to see the results, you wouldn't
believe Access would be this confused.

The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.

Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)

IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.

If you have an explanation for Query1's results I'd appreciate it. I
suppose that the Dates are cast as Variants when it is run.

Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but
that returned 11/30/1999. Go figure.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Strange. It looks like the date column is being turned into a string
data type. Perhaps if you put #12/30/1899# instead of NULL the date
column will remain a date column. But, then you'll have to change your
criteria to

WHERE DF <> #12/30/1899# AND DF < Date()

instead of

Where DF < Date()

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQG6GqIechKqOuFEgEQJPBwCgqjmOt7V91ywHKbYQNK4zcJ z9OHwAoPQY
Id1wgOzrGeqEGQZb5Rc3olSo
=YYXS
-----END PGP SIGNATURE-----

Thanks MG. In most instances your solution would work. Unfortunately
there is another date field (I was using a simple example) that I also
filter on and using this method would exclude some records that would
need to be displayed. If I worked around that, a date from 1899 would
confuse the users. I do believe you are correct in correct that it is
converted to string. It's the only thing that makes sense....I kept
thinking in date terms but "4/2" would be greater than the string
"4/14"...thanks for pointing that out.

[Salad]

Steve Jorgensen wrote:

The problem is that when JET can't determine a single data type for a column,
it automatically converts it to a string. Null does not indicate a data type,
so Null as DF becomes a string. Subsequently, the union of DF becomes a
string as well.

The solution? There is an undocumented form if IIf that you can use in
queries with only 2 arguments. JET is smart enough to make the column use the
data type indicated by argment number 2, even though IIf returns Null when
argument 1 is False.

So, the reformulatd union query looks like this...

SELECT
Text1 As TF,
Date1 As DF
FROM Table1
UNION ALL SELECT
Text2 As TF,
Date2 As DF
FROM Table2
UNION ALL SELECT
Text3 As TF,
IIf(False,#1/1/2000#) As DF
FROM Table3
IIf(False,#1/1/2000#) generates a date column with all Null values. You can
do the same thing with other data types, e.g.
IIf(False,CLng(0))
IIf(False,CCur(0))
Wow! It is true you learn something new each day.

I kept staring at your code and thinking..."Steve's missing an argument
in all of his examples" then re-read where you stated it is an
undocumented form of IIF() and then I caught on...it's early in the
morning...I need some coffee. Your solution works like a champ.

Sometimes I wonder where you guys pick up this stuff...by trial and
error?...or simply by error. <g>

Any idea if a query like this would work if the data is stored in SQL
Server?


On Sat, 03 Apr 2004 06:13:33 GMT, Salad <oi*@vinegar.com> wrote:

A97.

Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:

Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)

The following makes up a saved query called Query1
Select Text1 As TF, Date1 As DF From Table1
UNION ALL
Select Text2 As TF, Date2 As DF From Table2
UNION ALL
Select Text3 As TF, Null As DF From Table3

Now if I run a query selecting records from Query1
Select * From Query1 Where DF < Date()
or use Query1 as a recordset in a form with a similar filter, records
with dates greater than today are displayed. I have checked many times
that Access considers 4/2/2004 greater than 4/14/2004. Ex:
Expr1: DF < Date()
Criteria True
Sure enough. The record with 4/15/2004 shows up.

If I remove the line
Select Text3 As TF, Null As DF From Table3
then the query works as advertized. Creating a Null column is the culprit.

This problem is common enough in Google...lots of people recommend using
CDate which is useless. Formatting is worthless. And unless you
actually run a similar query to see the results, you wouldn't believe
Access would be this confused.

The only solution I have been able to come up with to resolve this
problem is to create a second query and use convoluted logic. I call
the query Query2.

Select TF, IIF(IsDate(DF),DateSerial(Year(DF), _
Month(DF), Day(DF)),Null)

IOW, you need to check that the date field is a date and then convert
the date into a date if it is a date.

If you have an explanation for Query1's results I'd appreciate it. I
suppose that the Dates are cast as Variants when it is run.

Is there another way to create a blank date (not in the table, but in
the query) to avoid this situation? I tried DateSerial(0,0,0) but that
returned 11/30/1999. Go figure.

[Steve Jorgensen]

On Sat, 03 Apr 2004 14:51:09 GMT, Salad <oi*@vinegar.com> wrote:

Steve Jorgensen wrote:

The problem is that when JET can't determine a single data type for a column,
it automatically converts it to a string. Null does not indicate a data type,
so Null as DF becomes a string. Subsequently, the union of DF becomes a
string as well.

The solution? There is an undocumented form if IIf that you can use in
queries with only 2 arguments. JET is smart enough to make the column use the
data type indicated by argment number 2, even though IIf returns Null when
argument 1 is False.

So, the reformulatd union query looks like this...

SELECT
Text1 As TF,
Date1 As DF
FROM Table1
UNION ALL SELECT
Text2 As TF,
Date2 As DF
FROM Table2
UNION ALL SELECT
Text3 As TF,
IIf(False,#1/1/2000#) As DF
FROM Table3
IIf(False,#1/1/2000#) generates a date column with all Null values. You can
do the same thing with other data types, e.g.
IIf(False,CLng(0))
IIf(False,CCur(0))
Wow! It is true you learn something new each day.

I kept staring at your code and thinking..."Steve's missing an argument
in all of his examples" then re-read where you stated it is an
undocumented form of IIF() and then I caught on...it's early in the
morning...I need some coffee. Your solution works like a champ.

Sometimes I wonder where you guys pick up this stuff...by trial and
error?...or simply by error. <g>

In my case, yup, it was trial and error. First, I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get an error.
Then, I noticed that my query that had been returning strings in the column
was now showing numbers right-justified, indicating that the data type was now
numeric. It was one of the best serendipitous discoveries I've ever made
because I now use this trick constantly, and I've never seen anyone but me
suggest this trick.
Any idea if a query like this would work if the data is stored in SQL
Server?

Actually, it does, and it's a less obtuse solution there. In Transact and in
ANSI SQL, the Case .. When .. Then .. End structure does not even appear to
require a False part, and it is known to determine its data type by the When
<expr> and Else <expr> parts.

[Salad]

Steve Jorgensen wrote:

On Sat, 03 Apr 2004 14:51:09 GMT, Salad <oi*@vinegar.com> wrote:

Steve Jorgensen wrote:

The problem is that when JET can't determine a single data type for a column,
it automatically converts it to a string. Null does not indicate a data type,
so Null as DF becomes a string. Subsequently, the union of DF becomes a
string as well.

The solution? There is an undocumented form if IIf that you can use in
queries with only 2 arguments. JET is smart enough to make the column use the
data type indicated by argment number 2, even though IIf returns Null when
argument 1 is False.

So, the reformulatd union query looks like this...

SELECT
Text1 As TF,
Date1 As DF
FROM Table1
UNION ALL SELECT
Text2 As TF,
Date2 As DF
FROM Table2
UNION ALL SELECT
Text3 As TF,
IIf(False,#1/1/2000#) As DF
FROM Table3
IIf(False,#1/1/2000#) generates a date column with all Null values. You can
do the same thing with other data types, e.g.
IIf(False,CLng(0))
IIf(False,CCur(0))
Wow! It is true you learn something new each day.

I kept staring at your code and thinking..."Steve's missing an argument
in all of his examples" then re-read where you stated it is an
undocumented form of IIF() and then I caught on...it's early in the
morning...I need some coffee. Your solution works like a champ.

Sometimes I wonder where you guys pick up this stuff...by trial and
error?...or simply by error. <g>

In my case, yup, it was trial and error. First, I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get an error.

Help states "IIf always evaluates both truepart and falsepart, even
though it returns only one of them." I would never have considered
trying a double argument. Usually when I type an IIF() I spend more
time getting all the [] and () correct just so I can get to the next
field in the builder
Then, I noticed that my query that had been returning strings in the column
was now showing numbers right-justified,
That's RIGHT! My dates were left justified. In my data entry forms
dates are always left justified but not so in the query or table. I
should have noticed that.

indicating that the data type was now numeric. It was one of the best serendipitous discoveries I've ever made
because I now use this trick constantly, and I've never seen anyone but me
suggest this trick.

I think most people reading this thread will have learned something new
and worthwhile....depends how tricky they get with their queries.

Any idea if a query like this would work if the data is stored in SQL
Server?

Actually, it does, and it's a less obtuse solution there. In Transact and in
ANSI SQL, the Case .. When .. Then .. End structure does not even appear to
require a False part, and it is known to determine its data type by the When
<expr> and Else <expr> parts.

Thanks for this update on SQL Server.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:9g********************************@4ax.com:

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get
an error.

I've been using the 2-argument version for ages. Seriously. I never
thought it was anything but a supported method.

There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).

Perhaps I did this by analogy with spreadsheet IF() functions, where
the second argument is optional. But I don't see that document in
Excel's help, either, so maybe this goes all the way back to Lotus
123 days (which I haven't used since 1988).

I never thought there was anything odd about it, and have used it
quite frequently!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:hC******************@newsread1.news.pas.earth link.net:

Is there another way to create a blank date (not in the table, but
in the query) to avoid this situation? I tried DateSerial(0,0,0)
but that returned 11/30/1999. Go figure.

Everyone's already explained the problem for you and Steve has
offered a solution that works.

Another option would be to change the underlying format of the data
to sort correctly as text, or YYYY/MM/DD. I don't know if
table-defined formats survive UNIONing and type coercion, but it's a
possibility.

Nope, unfortunately, it doesn't work.

But it's another tool in the arsenal when you have uncontrollable
date-to-string conversion going on.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Salad]

David W. Fenton wrote:

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get
an error.

I've been using the 2-argument version for ages. Seriously. I never
thought it was anything but a supported method.

There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).

Perhaps I did this by analogy with spreadsheet IF() functions, where
the second argument is optional. But I don't see that document in
Excel's help, either, so maybe this goes all the way back to Lotus
123 days (which I haven't used since 1988).

I never thought there was anything odd about it, and have used it
quite frequently!

I think I need to hang out here more. Lot's of data to be gleaned from
this group.

[MGFoster]

Salad wrote:

David W. Fenton wrote:

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get
an error.

I've been using the 2-argument version for ages. Seriously. I never
thought it was anything but a supported method.
There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).
Perhaps I did this by analogy with spreadsheet IF() functions, where
the second argument is optional. But I don't see that document in
Excel's help, either, so maybe this goes all the way back to Lotus
123 days (which I haven't used since 1988).
I never thought there was anything odd about it, and have used it
quite frequently!

I think I need to hang out here more. Lot's of data to be gleaned from
this group.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another use of the IIf(<expression>,<true>) is in SQL aggregate
functions. E.g.: Count(<column>) will only count the non-NULL values;
therefore, you could use Count(IIF(<expression>,1) to count all the True
evaluations of the expression. False evaluations will return NULL,
which won't be counted by Count().

I saw this example on this ng (I believe).

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQG9/uYechKqOuFEgEQK8dgCfWL2LvU++qDS9JpbcJ9x556suersAnA 58
1YfWg3Gzvd7IMolJ00vRgELE
=guzi
-----END PGP SIGNATURE-----

[Steve Jorgensen]

On Sat, 03 Apr 2004 21:57:13 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:9g********************************@4ax.com :

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't get
an error.

I've been using the 2-argument version for ages. Seriously. I never
thought it was anything but a supported method.

There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).

Perhaps I did this by analogy with spreadsheet IF() functions, where
the second argument is optional. But I don't see that document in
Excel's help, either, so maybe this goes all the way back to Lotus
123 days (which I haven't used since 1988).

I never thought there was anything odd about it, and have used it
quite frequently!

My fist exposure to IIf was in VBA where, of course, the 3rd argument is
required, so I never guessed that would be not required in queries. it
appears to be that the Expression service has its own version of IIf which
would explain why it works in calculated control expressions, etc.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:p6********************************@4ax.com:

On Sat, 03 Apr 2004 21:57:13 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:9g********************************@4ax.co m:

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't
get an error.
I've been using the 2-argument version for ages. Seriously. I
never thought it was anything but a supported method.

There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).

Perhaps I did this by analogy with spreadsheet IF() functions,
where the second argument is optional. But I don't see that
document in Excel's help, either, so maybe this goes all the way
back to Lotus 123 days (which I haven't used since 1988).

I never thought there was anything odd about it, and have used it
quite frequently!

My fist exposure to IIf was in VBA where, of course, the 3rd
argument is required, . . .

My first exposure to it was in expressions in controls and queries,
since I did lots of that kind of thing before I messed around with
any VBA. I can't see a whole lot of contexts in VBA where immediate
IF makes any sense, to be honest.
. . . so I never guessed that would be not
required in queries. it appears to be that the Expression service
has its own version of IIf which would explain why it works in
calculated control expressions, etc.

Hmm. I would be more inclined to think that it's not a different
version of it, but that the VBA syntax checking doesn't know
everything about the real capabilities of IIf(). That would be, I
think, due to a defective type library somewhere.

Or, it could have something to do with the requirements of VBA.

In any event, I can't think of circumstances where I'd use IIf() in
VBA at all (using it in dynamic SQL is different, as it's actually
going to be executed in the SQL, not in VBA).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Sun, 04 Apr 2004 22:03:01 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:p6********************************@4ax.com :

On Sat, 03 Apr 2004 21:57:13 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:9g********************************@4ax.com :

I accidentally discovered the
2-argument for of IIf when I typed it accidentally and didn't
get an error.

I've been using the 2-argument version for ages. Seriously. I
never thought it was anything but a supported method.

There are contexts where it's important to supply the second
argument, but I forget what those are (in queries?).

Perhaps I did this by analogy with spreadsheet IF() functions,
where the second argument is optional. But I don't see that
document in Excel's help, either, so maybe this goes all the way
back to Lotus 123 days (which I haven't used since 1988).

I never thought there was anything odd about it, and have used it
quite frequently!

My fist exposure to IIf was in VBA where, of course, the 3rd
argument is required, . . .

My first exposure to it was in expressions in controls and queries,
since I did lots of that kind of thing before I messed around with
any VBA. I can't see a whole lot of contexts in VBA where immediate
IF makes any sense, to be honest.

. . . so I never guessed that would be not
required in queries. it appears to be that the Expression service
has its own version of IIf which would explain why it works in
calculated control expressions, etc.

Hmm. I would be more inclined to think that it's not a different
version of it, but that the VBA syntax checking doesn't know
everything about the real capabilities of IIf(). That would be, I
think, due to a defective type library somewhere.

Or, it could have something to do with the requirements of VBA.

In any event, I can't think of circumstances where I'd use IIf() in
VBA at all (using it in dynamic SQL is different, as it's actually
going to be executed in the SQL, not in VBA).

I use it in VBA in cases where the True/False part expressions are small
constants, and where using If .. Then instead would make the code much larger,
and harder to read. It's the exception, not the rule, but it definitely comes
up now and again. It could be that I actually did not use IIf in VBA first,
but I used the VBA help on IIf, so I assumed its capabilities were only as
advertised.

The reason I think the espression service has its own IIf is because it does
short-circuit processing. The only way I can see that that could work is if
IIf is not processed as a "function" at all. To pass all 3 arguments to a
function, they would all have to be evaluated first. Additionally, a function
must return a typeless Null or a typed value, but SQL knows what type to give
to the -column- based on the types of the arguments passed to IIf, so SQL
(probably via the expression service) must also be evaluating IIf some other
way that as a simple function call.

[Michael \(michka\) Kaplan [MS]]

"David W. Fenton" <dX********@bway.net.invalid> wrote...

Hmm. I would be more inclined to think that it's not a different
version of it, but that the VBA syntax checking doesn't know
everything about the real capabilities of IIf(). That would be, I
think, due to a defective type library somewhere.

Actually, it is a different version. They are not the same function in any
way except name.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.