Date and Is Null Text Parameters Help

[davide507]

Hello,
I've created a select query with a date range parameter as well as a text field parameter

Between [Start Date] and [End Date]

The second parameter:

Like [Enter Accountant] or Like [Enter Accountant] Is Null.

When you run the query, and enter the range and the Accountant's name, it returns the accountants records for the specified date range. When you enter the date range and leave the accountant parameter blank, it returns all records regardless of the date range entered. How do I set this up to deliver all accountants records within the date range if the parameter is blank? Any help would be greatly appreciated!
David

[MSeda]

I can't say for sure with out seeing the sql but I think you have made a common error entering the criteria. In the access query design view on the first criteria row enter the Between [Start Date] and [End Date]
and under the accountant enter Like [Enter Accountant]
move to the next criteria row and again enter the Between [Start Date] and [End Date] and enter the like [Enter Accountant] is Null in the accountant column.
If you look at the SQL it should look some thing like:

Select [AuditTable].* FROM [AuditTable] WHERE (AuditDate between [Start Date] and [End Date] AND Accountant like [Enter Accountant]) OR (AuditDate between [Start Date] and [End Date] AND Accountant like [Enter Accountant] is Null)

[NeoPa]

Try :

Like Nz([Enter Accountant],'') & '*'

[hyperpau]

Hello,
I've created a select query with a date range parameter as well as a text field parameter

Between [Start Date] and [End Date]

The second parameter:

Like [Enter Accountant] or Like [Enter Accountant] Is Null.

When you run the query, and enter the range and the Accountant's name, it returns the accountants records for the specified date range. When you enter the date range and leave the accountant parameter blank, it returns all records regardless of the date range entered. How do I set this up to deliver all accountants records within the date range if the parameter is blank? Any help would be greatly appreciated!
David

You don't need complexities for this.

Remove the LIKE in the criteria. Just type in [Enter Accountant] OR Is Null

I tried this myself and I guarrantee you it will work but make sure that when user
inputs the accountant name, it should be the exact spelling or name.

Remember, if you use the LIKE parameter, you need a wild card (*).

ex: Like "st*" which would show results for all those records starting with the letters s and t. which is why it is impossible to use the LIKE parameter coz you would be stucked with fixed starting charactes.

[NeoPa]

I think perhaps you misunderstand something of what was posted before (and possibly of what's requested).
The solution I provided actually handled all the issues you brought up, and was also less complicated. Let me know if you would like a breakdown of how (or why) it works.