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

Microsoft Access - Parameter Query

P: 8
I am currently writing a query in Access. I need to set up a parameter query for three fields which are called Status (text field), Authorised (date field) and Legal notices (date field).

I need to set it up so that the user can enter info into each of the fields and run the query from that but I also need to set it up so that if nothing is entered into the parameter boxes that it needs to return all results.

I have set up the first parameter query for the status field which I have entered the text below into the criteria box. This appears to work ok.

Like "*" & [status] & "*"

When I try to use something similar on the other two fields to set up the parameter query the query doesn't return any results.
Jan 20 '10 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
Did you try to change the datatype of the date fields into a string ?
Just try the Format() function.

Nic;o)
Jan 22 '10 #2

P: 74
make your query criteria look like this
Expand|Select|Wrap|Line Numbers
  1. like "*" & IIF(Forms!MyFormName.DateFieldName is null,"",Forms!MyFormName.DateFieldName ) & "*"
Jan 23 '10 #3

P: 8
thanks for the replies. I will have another shot at it using your suggestions.

Thank you
Jan 23 '10 #4

ADezii
Expert 5K+
P: 8,597
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.Status, tblTest.Autorised, tblTest.[Legal Notices] _
  2. FROM tblTest _
  3. WHERE (tblTest.Status Like "*" & [Enter Status] & "*") OR _
  4. ( tblTest.Autorised Like "*" & [Enter Authorized Date] & "*") OR _
  5. (tblTest.[Legal Notices] Like "*" & [Enter Legal Notice Date] & "*");
Jan 23 '10 #5

Post your reply

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