Date Field conundrum

[Jeff C]

I have two tables of data joined in a union query, each of the tables have an
admit date and a birthdate field and the field type is date/time defined. I
have placed the resulting query in a new query in an attempt to return only
those records where the admit date and birthdate field are equal. I have
tried a number of different criteria but no matter what I've tried I get
data type mismatch etc type error messages. I have also tried using the
resulting recordset in a new query and qualifying each of the fields with
CDate but still cannot get a result without an error. I have tried creating
new queries and joining on acct# and also joing the admit date to the
birthdate. Same error message.

Anyone have an idea on a solution? Suggestion?

Thanks very much.

 

[Wayne-I-M]

Hi

No a very elegant method - but would work.

SELECT IIf([tblA]![DateA]=[tblB]![DateB],"yes","No") AS SomeName
FROM tblB INNER JOIN tblA ON tblB.IDField = tblB.IDField
WHERE (((IIf([tblA]![DateA]=[tblB]![DateB],"yes","No"))=Yes));

Just restrict with a simple criteria

Just a quick idea - there will be better methods but no time at the
moment

 

[Allen Browne]

So you want to UNION the records from 2 tables into one long list. Both
tables have AdmitDate and BirthDate fields (both Date/Time), and you only
want the records where both are fields equal:

SELECT AdmitDate, BirthDate
FROM Table1
WHERE AdmitDate = BirthDate
UNION ALL
SELECT AdmitDate, BirthDate
FROM Table2
WHERE AdmitDate = BirthDate;

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do


:

So you want to UNION the records from 2 tables into one long list.

This is a much better approach than what I was trying to do, separating out
the records using the original UNION query I created.

Both tables have AdmitDate and BirthDate fields (both Date/Time), and you
only
want the records where both are fields equal:

SELECT AdmitDate, BirthDate
FROM Table1
WHERE AdmitDate = BirthDate
UNION ALL
SELECT AdmitDate, BirthDate
FROM Table2
WHERE AdmitDate = BirthDate;

Worked without a hitch - don't know why I kept getting the error but you
gave me the solution.

Thanks very much Allen!