Results 1 to 6 of 6
  1. #1
    Boomer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    3

    How to create a parameter query to return all records if no value entered


    I am trying to create a query in design view (Access 2010) whereby the user is prompted for a city with a parameter prompt I want the user to see the results whereby all records for all cities will be returned if no value is entered. I am not sure how to enter the criteria in query design view. Can you help?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is one method:

    Use a parameter to return all records if Null
    http://access.mvps.org/access/queries/qry0001.htm


    I might have misread your question.

    Do you mean that you execute the query and a dialog box pops up, you enter a value, then the query runs??

    Post the SQL of your query......

  3. #3
    Boomer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    3
    Yes, when the user opens the query in the navigation pane, a dialog box opens up and prompts them for their input for a city. When they press Okay, the query runs. I tried [Enter a City or leave blank for all records and press Okay]. But I do not know how to enter criteria for no value being entered. My prompt for the city is in the Criteria row of the query design.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SELECT * FROM tablename WHERE City LIKE [Enter a City or leave blank for all records and press okay] & "*";
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Boomer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    3
    But using criteria in the query design window, how can I use the OR operater to create the prompt to ask the user to enter a city and, if no city entered, than all records will be returned?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't need OR by using the LIKE operator with wildcard.

    Did you try it?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. Replies: 1
    Last Post: 10-22-2012, 05:52 PM
  3. Replies: 3
    Last Post: 03-20-2012, 10:31 AM
  4. Replies: 3
    Last Post: 08-15-2011, 10:06 AM
  5. Replies: 5
    Last Post: 08-05-2009, 12:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums