formatting dates in union queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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....
 
Back
Top