how to filter calculated field in query using a criteria

[GinaWhipp]

Tip toeing in...

Umm, when I look at the query I see the very last field "Next Service". That is never going to work, you can't run Between against a Text field. What are you trying to do?

 

[GinaWhipp]

Nevermind, I fixed it...

SELECT MaskTbl.MaskID, MaskTbl.MaskNotes, DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")") AS FindLast, MaskTbl.ExMask, MaskTbl.MaskImpEmployeeID, DateAdd("m",[MaskServiceMonths],DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")")) AS NextService, [NextService]-Date() AS tillDays, MaskTbl.MaskType, MaskTbl.MaskSerialNumber, MaskTbl.MaskServiceMonths
FROM MaskTbl
WHERE (((MaskTbl.ExMask)=No) AND ((DateAdd("m",[MaskServiceMonths],DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")"))) Between Date() And Date()+30))
ORDER BY DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")");

I replaced the [FindLast] in the NextService with the actual function because you can't run criteria when you include a function within a function. You have to actually perform the calculation. Hope I made sense there...

 

[sspreyer]

thanks Gina !!!! works great!!
thanks VbaInt!! For your Help

cheers

shane

 

[GinaWhipp]

You're welcome!