By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Date and Is Null Text Parameters Help

P: 1
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
Jun 11 '07 #1
Share this Question
Share on Google+
4 Replies


MSeda
Expert 100+
P: 159
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:

Expand|Select|Wrap|Line Numbers
  1. 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)
Jun 11 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
Try :
Expand|Select|Wrap|Line Numbers
  1. Like Nz([Enter Accountant],'') & '*'
Jun 11 '07 #3

hyperpau
Expert 100+
P: 184
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.
Jun 13 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jun 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.