How to skip query parameters when not needed ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query supplies a report, sometimes I want to view all details and
somtimes flitered according to a criteria.I Don't want to copy the same query
for non filtered details. Is there any way to skip the query parameters to
view all the details.
 
If your field is text then in the criteria row of the grid use this --
Like [Enter criteria] & "*"
If your press the ENTER key it is the same as a null in the parameter.

If you are looking for a date then try this from Ken ---
Test for each parameter being NULL within parenthesised Boolean OR
expressions, e.g.

SELECT *
FROM Customers
WHERE (State = [Enter State:] OR [Enter State:] IS NULL)
AND (City = [Enter City:] OR [Enter City:] IS NULL);

If no value is entered for a parameter the parenthesised expression will
evaluate to TRUE for every row by virtue of the parameter being NULL, if a
value is entered for the parameter it will only evaluate to TRUE where the
value in the column matches the value entered, so the result will be
restricted to those rows.

Ken Sheridan
Stafford, England
 
KARL
Thank you very much it worked perfect. it was text for both text and date

M.A.Halim

KARL DEWEY said:
If your field is text then in the criteria row of the grid use this --
Like [Enter criteria] & "*"
If your press the ENTER key it is the same as a null in the parameter.

If you are looking for a date then try this from Ken ---
Test for each parameter being NULL within parenthesised Boolean OR
expressions, e.g.

SELECT *
FROM Customers
WHERE (State = [Enter State:] OR [Enter State:] IS NULL)
AND (City = [Enter City:] OR [Enter City:] IS NULL);

If no value is entered for a parameter the parenthesised expression will
evaluate to TRUE for every row by virtue of the parameter being NULL, if a
value is entered for the parameter it will only evaluate to TRUE where the
value in the column matches the value entered, so the result will be
restricted to those rows.

Ken Sheridan
Stafford, England

--
KARL DEWEY
Build a little - Test a little


M.A.Halim said:
I have a query supplies a report, sometimes I want to view all details and
somtimes flitered according to a criteria.I Don't want to copy the same query
for non filtered details. Is there any way to skip the query parameters to
view all the details.
Click to expand...
Click to expand...
 
When adding the parameter mentioned, be sure to use the full line Karl gave,
including the word LIKE. I tried it without it and it would not work.

Karl, you are a genious. I am self-taught in Access so I usually have to
stumble my way through things for hours on end. Your post really, really
helped me a lot.


KARL DEWEY said:
If your field is text then in the criteria row of the grid use this --
Like [Enter criteria] & "*"
If your press the ENTER key it is the same as a null in the parameter.

If you are looking for a date then try this from Ken ---
Test for each parameter being NULL within parenthesised Boolean OR
expressions, e.g.

SELECT *
FROM Customers
WHERE (State = [Enter State:] OR [Enter State:] IS NULL)
AND (City = [Enter City:] OR [Enter City:] IS NULL);

If no value is entered for a parameter the parenthesised expression will
evaluate to TRUE for every row by virtue of the parameter being NULL, if a
value is entered for the parameter it will only evaluate to TRUE where the
value in the column matches the value entered, so the result will be
restricted to those rows.

Ken Sheridan
Stafford, England

--
KARL DEWEY
Build a little - Test a little


M.A.Halim said:
I have a query supplies a report, sometimes I want to view all details and
somtimes flitered according to a criteria.I Don't want to copy the same query
for non filtered details. Is there any way to skip the query parameters to
view all the details.
Click to expand...
Click to expand...
 
Actually, you should use the alternate syntax proposed

SELECT *
FROM Customers
WHERE (State = [Enter State:] OR [Enter State:] IS NULL)
AND (City = [Enter City:] OR [Enter City:] IS NULL);

Using

SELECT *
FROM Customers
WHERE State LIKE [Enter State:] & "*"
AND City LIKE [Enter City:] & "*"

will not necessarily return every row: any row where State or City is Null
will be ignored.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KDecker said:
When adding the parameter mentioned, be sure to use the full line Karl
gave,
including the word LIKE. I tried it without it and it would not work.

Karl, you are a genious. I am self-taught in Access so I usually have to
stumble my way through things for hours on end. Your post really, really
helped me a lot.


KARL DEWEY said:
If your field is text then in the criteria row of the grid use this --
Like [Enter criteria] & "*"
If your press the ENTER key it is the same as a null in the parameter.

If you are looking for a date then try this from Ken ---
Test for each parameter being NULL within parenthesised Boolean OR
expressions, e.g.

SELECT *
FROM Customers
WHERE (State = [Enter State:] OR [Enter State:] IS NULL)
AND (City = [Enter City:] OR [Enter City:] IS NULL);

If no value is entered for a parameter the parenthesised expression will
evaluate to TRUE for every row by virtue of the parameter being NULL, if
a
value is entered for the parameter it will only evaluate to TRUE where
the
value in the column matches the value entered, so the result will be
restricted to those rows.

Ken Sheridan
Stafford, England

--
KARL DEWEY
Build a little - Test a little


M.A.Halim said:
I have a query supplies a report, sometimes I want to view all details
and
somtimes flitered according to a criteria.I Don't want to copy the same
query
for non filtered details. Is there any way to skip the query parameters
to
view all the details.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top