I have set the following criteria in a query field:
As you can probably see I want to say that if there are no results greater than or equal to start date, take the criteria as null, else use criteria as greater than or equal to start date. However, it's not producing the results that I intend - is this the right way of going about it? StartDate is a field elsewhere in the query.
Access doesn't like when you have "Null" as a possible result. Very often, when Access sees that, it will give you Null as a result no matter whether the condition of the IIf statement is fulfilled or not. You want something a bit different. Something like
Access doesn't like when you have "Null" as a possible result.
To be a little more specific, a criteria tells Access to both select rows for which the criteria is true and exclude rows for which the criteria is false.
So what is Access supposed to do with rows for which the criteria is null? Null is neither true nor false so Access doesn't know what to do. People often interpret null as meaning "I don't know," so by that logic you're telling the database "I don't know" whether you should include a row.
You're making this whole thing way more complicated than it needs to be by using Iif. There's a time and place for iif, and it's generally not when you have to do boolean logic. My reasoning here may be off because I don't have your actual SQL query to test, but hopefully it points you in the right direction.
As you can probably see I want to say that if there are no results greater than or equal to start date, take the criteria as null, else use criteria as greater than or equal to start date.
First, you need a subquery to see if there are no results >= start date. That means:
NOT EXISTS (SELECT * FROM YourTable WHERE YourColumn >= [Start Date])
You can put this inside a criteria.
Second, let's rephrase "take criteria as null" to mean ignore the criteria.
To rephrase your criteria: "if not exists (blah blah) then ignore this criteria, else return >= start date?"
Now, a more sensible way to do this would be:
if exists (blah blah) then return >= start date.
We can just throw out the whole "else ignore this."
If then statements are known as implication in logic. If a then b is often written as a -> b, or a implies b.
Access doesn't have an "implies" operator, but a implies b is logically equivalent to not a or b.
You can do or statements by simply putting two criteria in separate rows.
So the first row should be not exists (blah blah), and the second should be >= [start date].