How to skip query parameters when not needed ? in Access Database General Questions    |   |  Help   Search For:     In:   Office   Access Database     Access Database General Questions     Access Database New users     Access Database design     Access Database Forms     Access Database Forms Coding     Access Database Import/Export Data     Access Database Install and Configure     Access Database Macros     Access Database Programming     Access Database Queries     Access Database Reports     Access Database Security              M.A.Halim 4/5/2007 3:21 PM PST   Question   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.     Was this post helpful to you?         Reply |   Top           KARL DEWEY  4/5/2007 3:36 PM PST   Answer   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" wrote: > 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.     Was this post helpful to you?         Reply |   Top           M.A.Halim 4/5/2007 4:05 PM PST   KARL Thank you very much it worked perfect. it was text for both text and date M.A.Halim "KARL DEWEY" wrote: > 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" wrote: > > > 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.     Was this post helpful to you?         Reply |   Top           KDecker 5/28/2009 7:20 AM PST   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" wrote: > 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" wrote: > > > 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.     Was this post helpful to you?         Reply |   Top           Douglas J. Steele 5/28/2009 8:09 AM PST   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 http://I.Am/DougSteele (no private e-mails, please) "KDecker" <KDecker@discussions.microsoft.com> wrote in message news:08FA7DED-4010-4696-BBDA-D68E7D73E2C3@microsoft.com... > 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" wrote: > >> 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" wrote: >> >> > 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.     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies