IIfs and Dates in a query criteria

[MykelDL]

I've looked and looked, and am pulling my hair out over this one. My query works just fine until I introduce a little nuance, then I get no results. I just cannot figure out why. I've been pulling data into the query (it's an expense-based query). It works just fine when the expenses are in the past. I use the Criteria <=Date() on the field DateofExp and all works as expected; I get the list of expenses that have occurred for the individual in question.

I have a situation where I need to identify expenses that will occur within a month for certain individuals. If I just change the Criteria to <=DateAdd("m",1,Date()) it works as expected--showing all the expenses that have occurred or will occur in the next month. But not all account qualify for preemptive reimbursement, so I have an identifier to discern between the account types. I don't want non-qualified expenses to show up in my query.

So I think, "Simple, Just used an IIF statement to test for the reimbursement option and use the criteria that applies. ReimbursementOption is the field that shows what I need to test and "2" is the option that allows for pre-payment, so I set the Criteria to IIf(DLookUp("[ReimbursementOption]","[tblBookings]")=2,<=DateAdd("m",1,Date()),<=Date()) and I get... nada.



Now when I was first writing the Criteria, I did it as follows, with a mistake, IIf(DLookUp("[ReimbursementOption]","[tblBookings]")<>2,<=Date)(),Date()+30) and because one of the expenses was exactly 30 days away, it worked. It just doesn't seem to like the combination of all the operators together. I'm starting to feel like I can't effectively use DLookUp in a query Criteria.

I've resorted to doing a test upon selecting the "run query" action to decide which of two queries to run, either the one with the criteria <=Date and a new one with the Criteria <=Date+30 (or <=DateAdd("m",1,Date())] ) but it still vexes me.

[aytee111]

The DLookup format is incorrect:
DLookup("fieldname","tablename","criteria(same as SQL but without the WHERE)")

DLookup is exceedingly slow and must only be used with a few records. Better to bring the table in to the query.

[CJ_London]

I agree with aytee, bring tblbookings into your query and join on whatever the link is. Then your calculation would be

<=dateadd("m",-ReimburstmentOption<>2,date())

if ReimbursementOption<>=2 it will return -(-1) i.e. 1. Otherwise it will return 0

[MykelDL]

I brought the ReimbursementOption field into the query and said IIf([tblBookings].[ReimbursementOption]=2,<=Date+30,<=Date). The result is the same as above, no joy.

[CJ_London]

as an alternative to my suggestion in post#3, your formula is wrong if this is wat you are using in the query grid. It should be

<= IIf([tblBookings].[ReimbursementOption]=2,Date()+30,Date()).

[MykelDL]

Ah, that may be it. I'll have to try that. Thanks.