Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    64

    Unanswered: Include all if query parameter is left blank

    I have a database with a form where you can select either a building number or room number in two seperate combo boxes. I have my query call those and I have listed them in the same row so that it is an and query instead of an or query. the reason I have to do this is if I put them in separate rows to make it an or query, you can put building 1 and room 2 in the form and get back all entries in building one and all entries in other buildings with that room number.

    However, with it as an and query, if I leave either option blank I don't get any results. So I need to add something to my query that says if left blank then show all. That way I can put the building number and see everything in that building.
    Code:
    SELECT user.UserID, user.FirstName, user.LastName, user.MiddleInitial, user.Comment, inventory.ItemID, inventory.Description, inventory.SerialNumber, inventory.ModelNumber, inventory.PropertyNumber, inventory.Cost, inventory.Building, inventory.Room, inventory.DateAcquired, inventory.Life, inventory.ScanDate, inventory.ScanType, inventory.Comment, inventory.Picture
    FROM [user] INNER JOIN inventory ON user.UserID = inventory.UserID
    WHERE (((inventory.Building)=[Forms]![LocationSelect]![Text7]) AND ((inventory.Room)=[Forms]![LocationSelect]![Text9]));
    My code for just the building line of the query is
    Code:
    [Forms]![LocationSelect]![Text7]
    And the code for just the room line of the query is
    Code:
    [Forms]![LocationSelect]![Text9]

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    May 2012
    Posts
    64
    It worked! thanks!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •