Union query is turning my dates into text

[Dragonous24]

So i have 2 Queries i'm combining into 1 with a Union Query.
Table 2 has a date field that Table 1 doesn't and so it needs to be created/defined in table 1 which i have done like this: ""as [fld_Date_trec]
But this is making that column a text field and so the dates from table 2 don't act as date, they act as text.

How do i change this so the field is defined as a date field.

Full Union SQL below.

SELECT [qryP+G+Treq].fld_GroupID_gm, [qryP+G+Treq].FirstOffld_CourseID_treq, [qryP+G+Treq].fld_ID_pers, [qryP+G+Treq].fld_Name_group, [qryP+G+Treq].fld_Surname_pers, [qryP+G+Treq].fld_Forename_pers, [qryP+G+Treq].fld_EmployedFrom_pers, [qryP+G+Treq].fld_EmployedTo_pers, [qryP+G+Treq].fld_Name_course, "" as [fld_StatID_trec], "" as [fld_Name_stat],""as [fld_Date_trec], "" as [fld_Lifespan_course], "" as [Valid_Until], "" as [Valid]
FROM [qryP+G+Treq]
WHERE ((( [qryP+G+Treq].FirstOffld_CourseID_treq) is not null));
UNION
SELECT [qryP+G+Trec].fld_GroupID_gm, [qryP+G+Trec].fld_CourseID_trec, [qryP+G+Trec].fld_ID_pers, [qryP+G+Trec].fld_Name_group, [qryP+G+Trec].fld_Surname_pers, [qryP+G+Trec].fld_Forename_pers, [qryP+G+Trec].fld_EmployedFrom_pers, [qryP+G+Trec].fld_EmployedTo_pers, [qryP+G+Trec].fld_Name_course, [qryP+G+Trec].fld_StatID_trec, [qryP+G+Trec].fld_Name_stat, [qryP+G+Trec].fld_Date_trec,[qryP+G+Trec].fld_Lifespan_course, [qryP+G+Trec].Valid_Until, [qryP+G+Trec].Valid
FROM [qryP+G+Trec]
where ((( [qryP+G+Trec].fld_CourseID_trec) is not null))
ORDER BY [qryP+G+Treq].fld_Name_group, [qryP+G+Treq].fld_Surname_pers, [qryP+G+Treq].fld_Forename_pers;

 

[Isaac]

Try using Null instead, I believe this will allow the union to preserve the date format of the one dataset. At least I think it would in sql server...especially if you cast Null as date.
A union query has to return one coherent dataset, so it doesn't know what else to do other than what it's doing, when you try to stack a ZLS on top of a date........

 

[Dragonous24]

Try using Null instead, I believe this will allow the union to preserve the date format of the one dataset. At least I think it would in sql server...especially if you cast Null as date.
A union query has to return one coherent dataset, so it doesn't know what else to do other than what it's doing, when you try to stack a ZLS on top of a date........

When i do Null as [fld_Date_trec] the entire column is blank, the data from table 2 doesnt show up.

 

[Isaac]

Interesting. I guess Access doesn't have that particular handy feature. Try making the query with the valid Date values, the one 'on top'.

 

[theDBguy]

When i do Null as [fld_Date_trec] the entire column is blank, the data from table 2 doesnt show up.

Hmm, how about reversing the two tables? Put Table2 on top first? Just a thought.

 

[Dragonous24]

Interesting. I guess Access doesn't have that particular handy feature. Try making the query with the valid Date values, the one 'on top'.

That appears to have worked, thanks.

 

[Isaac]

Glad you got it worked out! Best of luck.

 

[Dragonous24]

Hmm, how about reversing the two tables? Put Table2 on top first? Just a thought.

yea this works. thanks

 

[theDBguy]

yea this works. thanks

Cheers!

 

[Deleted member 147267]

When i do Null as [fld_Date_trec] the entire column is blank, the data from table 2 doesnt show up.

It looks like you put it on the wrong query? I thought req table had no dates, not rec? According to M$ it should work as Null, but while composing this I see that reversing the table order seems to make it work. Would be interesting to know if it would have worked IF you actually put it on the wrong table and fixed that.

 

[Pat Hartman]

FYI - "" = ZLS (Zero Length String) and is INVALID as a value for anything numeric including a date field.

"" and Null are NOT the same thing. Just because a column looks "empty" doesn't mean you can tell what its actual value is. I personally NEVER allow ZLS in text fields for the simple reason that I don't want to continually figure out whether a field contains "" or Null and I would never want a required field such as CompanyName to be "empty" which allowing ZLS would permit.