how to filter calculated field in query using a criteria (1 Viewer)

GinaWhipp

AWF VIP
Local time
Yesterday, 22:10
Joined
Jun 21, 2011
Messages
5,901
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

AWF VIP
Local time
Yesterday, 22:10
Joined
Jun 21, 2011
Messages
5,901
Nevermind, I fixed it...

Code:
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... :D
 

sspreyer

Registered User.
Local time
Yesterday, 19:10
Joined
Nov 18, 2013
Messages
251
thanks Gina !!!! works great!!
thanks VbaInt!! For your Help

cheers

shane
 

Users who are viewing this thread

Top Bottom