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

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

iQA/AwUBQG6GqIechKq OuFEgEQJPBwCgqj mOt7V91ywHKbYQN K4zcJz9OHwAoPQY
Id1wgOzrGeqEGQZ b5Rc3olSo
=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.co m> 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQG6GqIechKq OuFEgEQJPBwCgqj mOt7V91ywHKbYQN K4zcJz9OHwAoPQY
Id1wgOzrGeqEGQZ b5Rc3olSo
=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..."Ste ve'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.co m> 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.co m> 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..."Ste ve'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.co m> 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,CL ng(0))
IIf(False,CC ur(0))
Wow! It is true you learn something new each day.

I kept staring at your code and thinking..."Ste ve's missing an argument
in all of his examples" then re-read where you stated it is an
undocumente d 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....d epends 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.c om:

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.co m> wrote in
news:hC******** **********@news read1.news.pas. earthlink.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.