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?
"[MVP] S.Clark" wrote:
> http://www.mvps.org/access/queries/qry0020.htm
>
> --
> Steve Clark, Access MVP
> FMS, Inc.
> www.fmsinc.com/consulting
>
> "Jeff C" <(E-Mail Removed)> wrote in message
> news:1F2AFF16-1C14-49D5-B33D-(E-Mail Removed)...
> > 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.
> >
> >
>
>
>
Similar Threads
