Results 1 to 6 of 6
  1. #1
    zy0n1c is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    AL
    Posts
    21

    Question Query requiring an input in my first 4 inputs. "Is Null" should allow me to leave it blank, right?

    Edit: - REalized my title said "first 4 inputs" - it's just the first one. It MUST have an input for State 1.

    When I run my query, I CANNOT leave State 1 blank. 2, 3, 4 - no problem.



    I'd like my users to be able to query both WITH and WITHOUT inputting a state first. I get a blank/empty table when queried without the state input.

    My code is below: (left out the SELECT statement because it was a basic, grab all these fields SELECT.)

    Code:
    WHERE (
    
    (
    (EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState1] 
    Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState2]) 
    Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState3]) 
    Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState4])) 
    )
    
    AND ((EmployeeDB.[Basic+]) Like IIf([forms]![EmployeeListMenu]![CheckBasicPlus],True,"*")
    AND ((EmployeeDB.TWIC) Like IIf([forms]![EmployeeListMenu]![CheckTWIC],True,"*")) 
    AND ((EmployeeDB.HAZWOPER) Like IIf([forms]![EmployeeListMenu]![CheckHAZWOPER],True,"*")
    ));

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It should be

    [Forms]![EmployeeListMenu]![ComboState1] Is Null Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState1]

    and ditto the other three.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    zy0n1c is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    AL
    Posts
    21
    Uh...well with my code in that format it's showing me every single state whether I input something or not...can you help me out with the parenthesis here too? That may be where I screwed up.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I always build it in the Query Designer and enter it in the criteria like so it ends up looking like

    = [Forms]![EmployeeListMenu]![ComboState1] or [Forms]![EmployeeListMenu]![ComboState1] Is Null

    Access then does the "Add a million brackets" trick on its own.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is preferred that you show all the code --not because we are nosy, but we like to get full context with questions.

    It is also helpful for you and readers if you provide an overview description of the "business" in simple English.

    Good luck.

  6. #6
    zy0n1c is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    AL
    Posts
    21
    I solved it with the following code:


    Code:
     SELECT EmployeeDB.[Last Name], EmployeeDB.[First Name], EmployeeDB.State, EmployeeDB.[Phone Number 1], EmployeeDB.[Phone Number 2], EmployeeDB.[Basic+], EmployeeDB.TWIC, EmployeeDB.HAZWOPER, EmployeeDB.Hydroblasting, EmployeeDB.[Scaffold Builder], EmployeeDB.[Confined Spaces], EmployeeDB.[OSHA 10], EmployeeDB.[OSHA 40], EmployeeDB.[CDL w/VAC], EmployeeDB.[CDL (standard)], EmployeeDB.[Valid Driver's License], EmployeeDB.[Job Types], EmployeeDB.[Site Specific Training 1], EmployeeDB.[Site Specific Training 2], EmployeeDB.[Site Specific Training 3], EmployeeDB.[Site Specific Training 4], EmployeeDB.[Site Specific Training 5], EmployeeDB.[Site Specific Training 6], EmployeeDB.[Eligible for Rehire], EmployeeDB.Status
    FROM EmployeeDB
    WHERE (
    
    (([Forms]![EmployeeListMenu]![ComboState1] Is Null) And ([Forms]![EmployeeListMenu]![ComboState2] Is Null) And ([Forms]![EmployeeListMenu]![ComboState3] Is Null) And ([Forms]![EmployeeListMenu]![ComboState4] Is Null)) Or ((EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState1]) Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState2]
    Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState3] Or EmployeeDB.State = [Forms]![EmployeeListMenu]![ComboState4]));
    Thank you for your help - it did help in the end, just in a different way!

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

Similar Threads

  1. How to show BLANK (null) counts as ZERO "0"
    By KingOf206 in forum Queries
    Replies: 17
    Last Post: 07-25-2017, 03:52 PM
  2. Replies: 6
    Last Post: 06-24-2016, 06:07 AM
  3. Replies: 4
    Last Post: 01-22-2015, 10:30 AM
  4. Passing criteria "NULL" or "IS NOT NULL" to a query
    By SgtSaunders69 in forum Forms
    Replies: 1
    Last Post: 12-24-2011, 02:22 AM
  5. Replies: 1
    Last Post: 08-02-2011, 07:21 AM

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
  •  
Other Forums: Microsoft Office Forums