USER ENTRY PROMPT Criteria for ALL Possible values (except 0 or null)

[kev100 via AccessMonster.com]

I have a query that prompts the user to enter 1 or more Route #'s to be
included in the query.


The table being queried has a field in every record named ROUTE. The Route
number could be anything from 1 to 1000 (or so).


If they just what to see data for 1 route...they can just enter 1 number (e.g.
"5" <ENTER>) etc.

If they need to see data for several routes....they can enter the route #'s
seperated by commas (e.g. 5,6,9,10,345,2...etc). and the query will display
the data for only those.

Here is the SQL code which enables that feature....it was very generously
provided by Duane Hookom earlier:

WHERE (((InStr("," & [Enter Routes with comma between] & ",","," & CDbl(
[MyTable.Route]) & ","))<>False)

Question:
Is it possible to adjust the above code so that if the user just hits <ENTER>.
.....the query will show the data for ALL routes except for 0 or Null ?

That would really help.....currently, I have 2 different queries......one for
"Select Routes" and another for "ALL Routes." Having 2 separate queries for
basically the same function is beginning to cause issues as this project
grows.

Thanks very much.

 

[John Spencer]

WHERE (((InStr("," & [Enter Routes with comma between] & ",","," & CDbl(
[MyTable.Route]) & ","))<>False) OR ( [Enter Routes with comma between] is
Null AND [MyTableRoute] <> 0)


Warning as you add more criteria with this kind of where statement, your
query will rapidly grow more complex until the query will fail.

 

[kev100 via AccessMonster.com]

Thanks VERY much....that worked GREAT......exactly what was needed !

Not sure if it's my version of Access (2002)....but I did need to remove 1 of
the ('s......the one right before "OR"

I used....

WHERE (((InStr("," & [Enter Routes with comma between] & ",","," & CDbl(
[MyTable.Route]) & ","))<>False) OR [Enter Routes with comma between] isNull
AND [MyTableRoute] <> 0)


Again....thanks very much.....

Kev100

 

[John Spencer]

I would have added a closing parentheses at the end. The parentheses group
the criteria together and what you want is either your original criteria or
where the parameter is null and MyTableRoute is not zero. Luckily, the way
the boolean logic works seems to be from left to right so you got the same
results without the parenthetical grouping.

WHERE (((InStr("," & [Enter Routes with comma between] & ",","," & CDbl(
[MyTable.Route]) & ","))<>False) OR ([Enter Routes with comma between]
isNull
AND [MyTableRoute] <> 0))