Hey all, hopefully this is a quick one!
I have a query with fields:
InspectionType (integer)
RndInspecNum (integer)
I have a table with fields :
RndInspecNum (integer)
2 Year (Boolean)
5 Year (Boolean)
Decennial (Boolean)
and I would like to filter the query so that if
InspectionType = 4 then only return if TRUE in the 2 Year field for that RndInspecNum
InspectionType = 5 then only return if TRUE in the 5 Year field for that RndInspecNum
InspectionType = 7 or >=9 then only return if TRUE in the Decennial field for that RndInspecNum
What would be the best way to do this? I had a messy field in the query that looked like this:
But it slowed the query to a CRAWL. I’m sure there is a way - as the data is all right there – but I’ll be damned if I can figure out how to fit it together!
Thanks in advance! Hopefully I have provided enough info for you!
I have a query with fields:
InspectionType (integer)
RndInspecNum (integer)
I have a table with fields :
RndInspecNum (integer)
2 Year (Boolean)
5 Year (Boolean)
Decennial (Boolean)
and I would like to filter the query so that if
InspectionType = 4 then only return if TRUE in the 2 Year field for that RndInspecNum
InspectionType = 5 then only return if TRUE in the 5 Year field for that RndInspecNum
InspectionType = 7 or >=9 then only return if TRUE in the Decennial field for that RndInspecNum
What would be the best way to do this? I had a messy field in the query that looked like this:
Code:
[SIZE=3][FONT=Times New Roman]IIf([tblInspection].[InspectionType]=4,DLookUp("TwoYear","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]=5,DLookUp("FiveYear","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]=7,DLookUp("Decennial","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]>=9,DLookUp("Decennial","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),-1))))[/FONT][/SIZE]
Thanks in advance! Hopefully I have provided enough info for you!