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.