formatting dates in union queries

[Guest]

I have created a union query from two other queries and am mergin a date
field and a text field. The output field consists of dates and null values.
I am trying to sort the dates but am struggling to convert to date format. i
used the DateValue function but this returns errors in the null fields and a
data type mismatch. Any suggestions as to how this might be sorted?

 

[Douglas J. Steele]

Without a little more details, I can't give specific advice, but in general,
you'd use the Format function on the date before you concatenate it:

ConcatenatedField: Format([MyDateField], "yyyymmdd") & [MyTextField]

You'd do this in each of the subqueries that make up the union query.

 

[Gary Walter]

PMFBI

It sounds like you want to end up
with a Date/Time column, but one
(or other, or both) column has NULL's in it?

tbl1 (field "DateTxt" is type Text)

ID DateTxt
1 1/1/2006
2 2/1/2006
3 NULL


tbl2 (field "ADate" is type Date/Time)

ID ADate
1 1/1/2006
2 NULL
3 3/1/2006

UNION query that returns Date/Time column:

SELECT
1 AS tbl,
IIf(IsDate([DateTxt]),CDate([DateTxt]),IIf(True,Null,CDate('9/9/9999'))) AS
dte
FROM tbl1
UNION
SELECT
2,
IIf(IsDate([ADate]),[ADate], IIf(True,Null,CDate('9/9/9999')))
FROM tbl2
ORDER BY
dte,
tbl;

Returns:

tbl dte
1 NULL
2 NULL
1 1/1/2006
2 1/1/2006
1 2/1/2006
2 3/1/2006

This gets you "around the NULL's,"
you may still have to deal with text dates
that CDate mungs, or return of NULL
because IsDate can not recognize it....