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:

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(<expr ession>,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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQG9/uYechKqOuFEgEQK 8dgCfWL2LvU++qD S9JpbcJ9x556sue rsAnA58
1YfWg3Gzvd7IMol J00vRgELE
=guzi
-----END PGP SIGNATURE-----

[Steve Jorgensen]

On Sat, 03 Apr 2004 21:57:13 GMT, "David W. Fenton"
<dX********@bwa y.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.c om:

On Sat, 03 Apr 2004 21:57:13 GMT, "David W. Fenton"
<dX********@bw ay.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).

--
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********@bwa y.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********@b way.net.invalid > wrote:

Steve Jorgensen <no****@nospam. nospam> wrote in
news:9g***** *************** ************@4a x.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********@bwa y.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.