Sorting by Date in Union Query - it's sorting as text, not date/nu

[Guest]

I have several Union Queries, and one "Mega" Union joining all together.
Included is a date field. Each original date field from the source tables
are formatted as Date/Time. Why are the Union Queries treating it as Text?
I want to sort by date, and it isn't sorting correctly - it's sorting by text
and not date or number.

I tried creating a Select query off the Mega Union query, and the properties
of the Date field did not provide a list of formats for me to select from. I
still could not sort by date/number rather than text.

I am using Access 2002 on Windows XP.

 

[Allen Browne]

The data in the first SELECT of the union query determines the data type. If
there are lots of nulls in the first rows, Access can get it wrong.

Try typecasting the field:
SELECT SomeField, CVDate([MyDateField]) AS Field2, ...

 

[Guest]

Thanks! That worked! How did you learn about the CVDate thing? Is there a
list or something somewhere where I can learn more of that type of thing?

The data in the first SELECT of the union query determines the data type. If
there are lots of nulls in the first rows, Access can get it wrong.

Try typecasting the field:
SELECT SomeField, CVDate([MyDateField]) AS Field2, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have several Union Queries, and one "Mega" Union joining all together.
Included is a date field. Each original date field from the source tables
are formatted as Date/Time. Why are the Union Queries treating it as
Text?
I want to sort by date, and it isn't sorting correctly - it's sorting by
text
and not date or number.

I tried creating a Select query off the Mega Union query, and the
properties
of the Date field did not provide a list of formats for me to select from.
I
still could not sort by date/number rather than text.

I am using Access 2002 on Windows XP.

 

[Allen Browne]

There are a bunch of type-conversion functions--such as CLng(), CDbl(),
CCur()--and they are extremely useful with calculated fields to ensure
Access understands the data type correctly. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

CVDate() works well for dates because it still permits Nulls. The others do
not, so if you want to force a field to a number where all entries are null
in the first SELECT of a UNION query, the other trick is to use IIf() to
specify the data type. For example, instead of:
SELECT Field1, Null AS Field2 FROM Table1 UNION ...
if you want Field2 to be a Number, use:
SELECT Field1, IIf(False, 1, Null) AS Field2 FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! That worked! How did you learn about the CVDate thing? Is there
a
list or something somewhere where I can learn more of that type of thing?

The data in the first SELECT of the union query determines the data type.
If
there are lots of nulls in the first rows, Access can get it wrong.

Try typecasting the field:
SELECT SomeField, CVDate([MyDateField]) AS Field2, ...

I have several Union Queries, and one "Mega" Union joining all together.
Included is a date field. Each original date field from the source
tables
are formatted as Date/Time. Why are the Union Queries treating it as
Text?
I want to sort by date, and it isn't sorting correctly - it's sorting
by
text
and not date or number.

I tried creating a Select query off the Mega Union query, and the
properties
of the Date field did not provide a list of formats for me to select
from.
I
still could not sort by date/number rather than text.

I am using Access 2002 on Windows XP.