How to enter a Wildcard or Is Null into a query paramater ?

[Mel]

1 - The Query Parameter... [Enter City] will let you return records
for a city

But what if you decide you want All Cities...
- just entering * doesn't work
- and just pressing enter and leaving the field blank doesn't work.

2 - typing... is null... into the field won't return records with no
cities entered...

So the questions are...
How would you create a Queryu Paramater such that you could enter
something to return either...
- all cities
or
- records with no cities entered

Thanks for the help.

 

[Sylvain Lafontaine]

Not sure but I think it's Ctrl-0

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Or "" (two double quotes)

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[fredg]

1 - The Query Parameter... [Enter City] will let you return records
for a city

But what if you decide you want All Cities...
- just entering * doesn't work
- and just pressing enter and leaving the field blank doesn't work.

2 - typing... is null... into the field won't return records with no
cities entered...

So the questions are...
How would you create a Queryu Paramater such that you could enter
something to return either...
- all cities
or
- records with no cities entered

Thanks for the help.

Where [CityField] = [Enter The City] Or [Enter The City] is null

When prompted, either enter the city name or leave the prompt blank.

 

[Mel_3]

1 - And what about returning all records regardless of what is in the
City field... something or Null?

2 - And what about return all records with anything (except null) in
the City field?

 

[John W. Vinson]

1 - And what about returning all records regardless of what is in the
City field... something or Null?

Exactly what Fred said (which you evidently rejected without trying it):

Where [CityField] = [Enter The City] Or [Enter The City] is null

2 - And what about return all records with anything (except null) in
the City field?

LIKE [Enter The City]

will accept wildcards; typing * in response to the prompt will find all
non-Null values.

 

[Mel_3]

John,

I'm not sure 1 will work but I will try it in the morning. I'm
probably looking at it wrong.
It seems to me that 1 will return the name of a city if you enter
it... or will return the records with null values for the city
field...
But will not return All records regardless of what is in the city
field... data or no (null) data
But... again I'll test it in the morning.

2 looks good... forgot about using "like"

It's been a long time since I've done Access

Thanks to everyone for the help !

 

[John W. Vinson]

I'm not sure 1 will work

It will. The logic is a bit peculiar but it's worth understanding!

A SQL WHERE clause is a logical expression which returns a value of TRUE or
FALSE. If the expression is TRUE, the record is retrieved; if it is FALSE (or,
to be precise, either FALSE or NULL), the record will not be retrieved.

So the expression

Where [CityField] = [Enter The City] Or [Enter The City] is null


will be TRUE if either of two conditions is true: 1) the city field in the
table is equal to the text string entered by the user; or 2) if the user left
the [Enter the City] prompt blank and didn't type anything at all.

So if they don't type anything, the logical expression is TRUE and the record
is retrieved - regardless of what the field contains, since the second clause
of the OR is true regardless of table contents.