Create a Date field in a query

[aztrix]

ok i have two queries pulling information from two tables.

the two queries contain identical fields however query one contains and extra field called Date and is of type Date\Time (short date)

i need to union the two queries into one including the Date field, so i created an extra field in query two and called it Date: "" as so. i did this because the next step requires identical fields.

then i created the union query combining the two queries together. All looks great until i begin looking closer. the date field that is of type Date\time is converted to text during the union because the field Date in query two is text. This is a problem for me as i need to pull information from the unioned query via the date value.

so my question is this, is there any way within query two that i can create a field of type Date\Time (Short Date) ;like i would with text IE "Date: "" "

[aztrix]

Oh and i cant create the field Date within the table for Query Two, i need to be able to do it within the query itself.

And the date field within query two needs to remain null

[si_the_geek]

You have essentially told us the answer yourself... rather than putting a specific Text value ("") which is only compatible with Text based data types, put Null (no value) which is compatible with most data types.

[aztrix]

yup i understand what your saying Si thanks, but when i do create the field "Date: Date() Is Null" and then union the two queries, the conversion from Date to text still occurs. maybe im trying to do something that just isn't supposed to be done?

[si_the_geek]

show us your current code/query

[aztrix]

ok attached is an image of the first and second query (i refereed to them as tables in the question) and the resulting unioned query

[aztrix]

its very subtle but the formatting of the date from right aligned to left aligned is a dead give away that the format has changed from Date to text, thus making and attempt at determining all the records between date X and Date Y impossible.

Is there a way i can stop this so the format remains Date?

[si_the_geek]

Those screenshots are nice as background info, but they are not really useful - what matters is the SQL of all of the queries.

[aztrix]

Si the First Query SQL is as follows;

SELECT qryAssetsAttachedRules_Active04.AppliesTo, qryAssetsAttachedRules_Active04.HostID, qryAssetsAttachedRules_Active04.RuleName, qryAssetsAttachedRules_Active04.TypeDefinition, qryAssetsAttachedRules_Active04.RuleExpires, qryAssetsAttachedRules_Active04.DateBooked, qryAssetsAttachedRules_Active04.DateCompleted, qryAssetsAttachedRules_Active04.ValidForPeriod, qryAssetsAttachedRules_Active04.ExpiryDate, qryAssetsAttachedRules_Active04.RuleExpired, qryAssetsAttachedRules_Active04.Value, qryAssetsAttachedRules_Active04.ReminderDate, qryAssetsAttachedRules_Active04.RuleReminder, qryAssetsAttachedRules_Active04.SkillsMaintenance, qryAssetsAttachedRules_Active04.AssetName, qryAssetsAttachedRules_Active04.AttachedRuleRecordID, qryAssetsAttachedRules_Active04.RuleRecordID, qryAssetsAttachedRules_Active04.Certificate, qryAssetsAttachedRules_Active04.FollowUpVal, qryAssetsAttachedRules_Active04.CurrentStatus, qryAssetsAttachedRules_Active04.Status, qryAssetsAttachedRules_Active04.Location, qryAssetsAttachedRules_Active04.Export
FROM qryAssetsAttachedRules_Active04;

the second query sql is a as follows;

SELECT qryAssetsAttachedRules_Active02No.AppliesTo, qryAssetsAttachedRules_Active02No.HostID, tblRuleDefinitions.RuleName, tblRuleDefinitions.TypeDefinition, tblRuleDefinitions.RuleExpires, qryAssetsAttachedRules_Active02No.DateBooked, qryAssetsAttachedRules_Active02No.DateCompleted, "" AS ValidForPeriod, Date() Is Null AS ExpiryDate, "No" AS RuleExpired, "" AS [Value], "" AS ReminderDate, "" AS RuleReminder, "" AS SkillsMaintenance, tblAssets.AssetName, qryAssetsAttachedRules_Active02No.AttachedRuleRecordID, qryAssetsAttachedRules_Active02No.RuleRecordID, qryAssetsAttachedRules_Active02No.Certificate, "0" AS FollowUpVal, tblAssets.CurrentStatus, tblRuleDefinitions.Status, tblAssets.Location, tblRuleDefinitions.Export
FROM tblAssets INNER JOIN (qryAssetsAttachedRules_Active02No INNER JOIN tblRuleDefinitions ON qryAssetsAttachedRules_Active02No.RuleRecordID = tblRuleDefinitions.RuleRecordID) ON tblAssets.AssetRecordID = qryAssetsAttachedRules_Active02No.HostID;

and the unioned query sql is as follows;

SELECT qryAssetsAttachedRules_Active05No.AppliesTo, qryAssetsAttachedRules_Active05No.HostID, qryAssetsAttachedRules_Active05No.RuleName, qryAssetsAttachedRules_Active05No.TypeDefinition, qryAssetsAttachedRules_Active05No.RuleExpires, qryAssetsAttachedRules_Active05No.DateBooked, qryAssetsAttachedRules_Active05No.DateCompleted, qryAssetsAttachedRules_Active05No.ValidForPeriod, qryAssetsAttachedRules_Active05No.ExpiryDate, qryAssetsAttachedRules_Active05No.RuleExpired, qryAssetsAttachedRules_Active05No.Value, qryAssetsAttachedRules_Active05No.ReminderDate, qryAssetsAttachedRules_Active05No.RuleReminder, qryAssetsAttachedRules_Active05No.SkillsMaintenance, qryAssetsAttachedRules_Active05No.AssetName, qryAssetsAttachedRules_Active05No.AttachedRuleRecordID, qryAssetsAttachedRules_Active05No.RuleRecordID, qryAssetsAttachedRules_Active05No.Certificate, qryAssetsAttachedRules_Active05No.FollowUpVal, qryAssetsAttachedRules_Active05No.CurrentStatus, qryAssetsAttachedRules_Active05No.Status, qryAssetsAttachedRules_Active05No.Location, qryAssetsAttachedRules_Active05No.Export
FROM qryAssetsAttachedRules_Active05No;
UNION SELECT qryAssetsAttachedRules_Active05Yes.AppliesTo, qryAssetsAttachedRules_Active05Yes.HostID, qryAssetsAttachedRules_Active05Yes.RuleName, qryAssetsAttachedRules_Active05Yes.TypeDefinition, qryAssetsAttachedRules_Active05Yes.RuleExpires, qryAssetsAttachedRules_Active05Yes.DateBooked, qryAssetsAttachedRules_Active05Yes.DateCompleted, qryAssetsAttachedRules_Active05Yes.ValidForPeriod, qryAssetsAttachedRules_Active05Yes.ExpiryDate, qryAssetsAttachedRules_Active05Yes.RuleExpired, qryAssetsAttachedRules_Active05Yes.Value, qryAssetsAttachedRules_Active05Yes.ReminderDate, qryAssetsAttachedRules_Active05Yes.RuleReminder, qryAssetsAttachedRules_Active05Yes.SkillsMaintenance, qryAssetsAttachedRules_Active05Yes.AssetName, qryAssetsAttachedRules_Active05Yes.AttachedRuleRecordID, qryAssetsAttachedRules_Active05Yes.RuleRecordID, qryAssetsAttachedRules_Active05Yes.Certificate, qryAssetsAttachedRules_Active05Yes.FollowUpVal, qryAssetsAttachedRules_Active05Yes.CurrentStatus, qryAssetsAttachedRules_Active05Yes.Status, qryAssetsAttachedRules_Active05Yes.Location, qryAssetsAttachedRules_Active05Yes.Export
FROM qryAssetsAttachedRules_Active05Yes;

hope this helps

[si_the_geek]

This is the most important section:

"" AS ValidForPeriod, Date() Is Null AS ExpiryDate, "No" AS RuleExpired, "" AS [Value], "" AS ReminderDate, "" AS RuleReminder, "" AS SkillsMaintenance,

Most of those are being set to empty String values, so will be treated as text based.

The condition for ExpiryDate (Date() Is Null) actually makes it a Boolean, and due to the condition will always return False (the function Date() will always return an actual value, never a Null). What you should have for it is this:

, Null AS ExpiryDate

[aztrix]

i see your point Si and that works well for the query before it is unioned together with the first query as once they are combined the "Null" ExpiryDate field in the second query overrides the "Date" ExpiryDate field of the first and the result is no dates at all.

thanks for your help i appreciate it.

[aztrix]

did you have any more thought on this Si?

[si_the_geek]

I'm afraid not, hopefully somebody else will spot something.