I have created a query that will pull data from a local table based on the values in a form control. Basically, the user enters the date they want to search in the text box, and the query only pulls data that has that date. The criteria ends up looking something like this:
IIF(ISNULL(textbox.value),>#1/1/1901#,textbox.value)
The problem arises when the text box is blank. I want to set up a criteria like > #1/1/1901# or "Is Not Null" so that it will pull all data. If I put any of these criteria in by themselves (without the IIF statement), they work perfectly. It even works when I put in a single value (i.e. #12/4/2013# pulls only the data from December 4, 2013; note that there is no ">" in that statement).
Basically, the IIF statement works, and both the "true" and "false" statements operate correctly. The problem is that Access is not correctly reading the ">" character if it is within an IIF statement.
Any help overcoming this issue will be greatly appreciated. Thanks in advance.
IIF(ISNULL(textbox.value),>#1/1/1901#,textbox.value)
The problem arises when the text box is blank. I want to set up a criteria like > #1/1/1901# or "Is Not Null" so that it will pull all data. If I put any of these criteria in by themselves (without the IIF statement), they work perfectly. It even works when I put in a single value (i.e. #12/4/2013# pulls only the data from December 4, 2013; note that there is no ">" in that statement).
Basically, the IIF statement works, and both the "true" and "false" statements operate correctly. The problem is that Access is not correctly reading the ">" character if it is within an IIF statement.
Any help overcoming this issue will be greatly appreciated. Thanks in advance.