Date Format/Problem In Query

[Guest]

The following is the query I am working with:

SELECT Child.ChildTable_ID, Child.ParentTable_ID, Child.Administered,
Child.AdminBy, Child.ReadOn, Child.ReadBy, Child.Induration,
Child.NegativePPD, Child.PositivePPD,
DMax("[ReadOn]","Child","[ParentTable_ID] =" & [ID]) AS [Current TB Test
Date], [All Employees].ID, [All Employees].Last, [All Employees].First, [All
Employees].MI, [All Employees].Dept, [All Employees].Questionaire, [All
Employees].[Annual Test], [All Employees].[Bi-Annual Test], [All
Employees].Active, [All Employees].Termed, [All Employees].Suspended, [All
Employees].[Contract Employee], [All Employees].[Agency Employee], [All
Employees].[Agency Name], [All Employees].[Date Of Hire], [All
Employees].[Drug Screen], [All Employees].[Positive PPD], [All
Employees].[Negative PPD]
FROM [All Employees] INNER JOIN Child ON [All Employees].ID =
Child.ParentTable_ID
ORDER BY Child.ReadOn DESC;

This query is the basis for a subform and the “DMax†expression for
[Current TB Test Date] nicely isolates the most current inoculation for each
employee record.

I need to be able to use this [Current TB Test Date] field in other queries
for reports where I use expressions in the “Criteria:†. I cannot come up
with an expression to give me:
All dates older than 6 months ago this month OR All dates older than 12
months ago this month.
I believe it has to do with date format problems which I really do not
understand nor have I any idea how to solve. I have tried using:
[Current TB Test Date]: CDate (DMax("[ReadOn]","Child","[ParentTable_ID] ="
& [ID])) in my criteria for the query above but this gives me errors. I seem
to be going in circles. I tried using just one report and feeding different
date expressions through an unbound form but that produced errors also. Any
help or ideas appreciated. Thank You.

 

[[MVP] S.Clark]

http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

I always get into uncharted waters, interesting.....following the article I
ended up with the following query:

SELECT [All Employees].Last, [All Employees].First, [All Employees].Dept,
[3_1Test].MaxOfReadOn, [All Employees].Active, [All Employees].[Bi-Annual
Test], [All Employees].[Annual Test]
FROM (3_1Test INNER JOIN [All Employees] ON [3_1Test].ID = [All
Employees].ID) INNER JOIN Child ON (Child.ReadOn = [3_1Test].MaxOfReadOn) AND
([All Employees].ID = Child.ParentTable_ID)
WHERE ((([3_1Test].MaxOfReadOn) Between
DateSerial(Year(Date()),Month(Date())-5,1) And
DateSerial(Year(Date()),Month(Date())-4,0) Or
([3_1Test].MaxOfReadOn)=Month(Date())+"1" & Year(Date())-"1") AND (([All
Employees].Active)=Yes) AND (([All Employees].[Bi-Annual Test])=Yes)) OR
((([All Employees].Active)=Yes) AND (([All Employees].[Annual Test])=Yes));

When I try and run the query I get data type mismatch. What am I not
understanding?

http://www.mvps.org/access/queries/qry0020.htm

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

The following is the query I am working with:

SELECT Child.ChildTable_ID, Child.ParentTable_ID, Child.Administered,
Child.AdminBy, Child.ReadOn, Child.ReadBy, Child.Induration,
Child.NegativePPD, Child.PositivePPD,
DMax("[ReadOn]","Child","[ParentTable_ID] =" & [ID]) AS [Current TB Test
Date], [All Employees].ID, [All Employees].Last, [All Employees].First,
[All
Employees].MI, [All Employees].Dept, [All Employees].Questionaire, [All
Employees].[Annual Test], [All Employees].[Bi-Annual Test], [All
Employees].Active, [All Employees].Termed, [All Employees].Suspended, [All
Employees].[Contract Employee], [All Employees].[Agency Employee], [All
Employees].[Agency Name], [All Employees].[Date Of Hire], [All
Employees].[Drug Screen], [All Employees].[Positive PPD], [All
Employees].[Negative PPD]
FROM [All Employees] INNER JOIN Child ON [All Employees].ID =
Child.ParentTable_ID
ORDER BY Child.ReadOn DESC;

This query is the basis for a subform and the "DMax" expression for
[Current TB Test Date] nicely isolates the most current inoculation for
each
employee record.

I need to be able to use this [Current TB Test Date] field in other
queries
for reports where I use expressions in the "Criteria:" . I cannot come up
with an expression to give me:
All dates older than 6 months ago this month OR All dates older than 12
months ago this month.
I believe it has to do with date format problems which I really do not
understand nor have I any idea how to solve. I have tried using:
[Current TB Test Date]: CDate (DMax("[ReadOn]","Child","[ParentTable_ID]
="
& [ID])) in my criteria for the query above but this gives me errors. I
seem
to be going in circles. I tried using just one report and feeding
different
date expressions through an unbound form but that produced errors also.
Any
help or ideas appreciated. Thank You.