Query with optional parameters? 1

[irishandy]

I've got a table that I want to get information from. Within the table there are five fields that need to be queried. However, the user should be able to disregard a parameter if they don't care about that field. It is a simple database that I'm setting up for someone to hold some very basic information - I need it to be idiotproof, i.e. they shouldn't have to know about making their own queries and things.

e.g.

Fields: USER_ID; PAYMENT; LOCATION; DATE; MISC

User #1 wants to specify criteria for all five fields.
User #2 wants to specify a user id and a location but doesn't care about anything else.

How can I set a query up so that this can be achieved? I had tried putting in "Null OR [Enter User ID]" however this doesn't work when you leave the parameter box blank...

Thanks for any and all replies.

 

[CosmoKramer]

Try changing your criteria to include the Like operator:

Like [Enter User ID] & "*"

If the user enters an ID that data will be returned. If they don't enter one, all IDs will.....

Hoc nomen meum verum non est.

 

[irishandy]

Excellent - for this job that works a treat. Thanks.

 

[PHV]

for this job that works a treat
Even for the date and the numeric fields ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[irishandy]

Good point.

UserID was a bad example - the fields that need to be queried are all 1 character fields, e.g. 'R' or 'P', except the date field. So, it's fine for all of them. This method works for the date field when you either enter a specific date or no date at all (which is still fine) however I can't specify things like ">01/01/1940". Is there any way of doing so relatively easily?