Parameters Question: How to enter All, is null,

[Rx_]

What are the key words in a Parameter for
* - all records
*123* - all records like 123
Is Null - records with null
Not is Null - has a value


Created a query. It has values returned in most records.
Added a parameter [Enter Value] in Column SomeInfo

Run the query - Enter Parameter Value box for Enter Value appears.
If I enter an exact value that exist in that field - it works!

If it is not the exact match - I can' t seem to get any records returned

 

[Brianwarnock]

Not able to test things at the moment but going from memory not sure you can do all that you want with one simple query.

The normal criteria to return specific or all records is

Field= [ enter value] or [enter value] is null

Thus to return all records don't put a value in the parameter, but you could easily make that ="*" , Or any other value the point being that you will select all records if [enter value] has that value.

To get partial matches you need to use Like

Like "*" & [enter value] &"*"

But this means that you can no longer get only exact matches, also the use of Like I think excludes null values so this cannot be used to obtain all records.

Also the use of Like means that indexes aren't used thus slowing large searches.

As you can see it is not so simple, but maybe somebody has a solution that I have not thought of or come across.

Brian

 

[Rx_]

On Access 2010 - Like "*" & [Enter] & "*" worked

B.T.W. for an Access Query Criteria this is an excellent site for how to use it:
http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

The Parameter can use something like: =Forms!CustomerF!CustomerID
because the value of customer ID would exist.

Parameter Query with Wildcards - please try
Would you please read this article and try this? I just can't get it to work on Access 2010
http://www.spearsofttech.com/articl...ery-tip-using-wildcards-in-parameter-queries/
Access Query - Using wildcards in Parameter Queries

Thanks so much

 

[Rx_]

http://www.databasedev.co.uk/null_query_parameter.html
Null Responses in a Parameterized Query - of interest
Can't get this to work either - but, it is not what I was looking for.

 

[Brianwarnock]

Could not get the first to work , didn't even ask for prompt.
the scond worked but you don't need the Like for it to work, and as that involves a function call it will be less efficient.

Brian