Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Null as option in query (2002)

    Hi,

    I'm trying get a query to filter for null records based on the answer that is in the control cmbReturn on frmPatientLookup. This is what I have so far:

    IIf([forms]![frmPatientLookup]![cmbReturn]="Non-Returned Records",Null,"*")

    My problem is that it doesn't filter for Null. I need to have only null records show if the answer chosen is "Non-returned Records".

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null as option in query (2002)

    You'll have to set it up differently: the WHERE part of the SQL should look like

    WHERE FieldName Is Null OR [Forms]![frmPatientLookup]![cmbReturn]<>"Non-Returned Records"

    Also see the screenshot below. FieldName is the name of the field you want to filter on, and tblSuppliers is the name of the table (just an arbitrary name for the screenshot)

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null as option in query (2002)

    Another new learning experience! This worked great. One more question. I will be setting up a similar scenerio for another query, but there will be 4 options available to the user to choose from, one of which will result in. Is this possible?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null as option in query (2002)

    One of which will result in ... what?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null as option in query (2002)

    The results would be result that shows all dates. Another will be records with "NA".

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null as option in query (2002)

    Sorry, you've lost me (please remember that English is not my native language). Could you explain slowly and in more detail? Thanks.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null as option in query (2002)

    "English is not my native language" - my respect for you has grown even more!!

    OK, Here goes. The next query I need to correct will filter records based on [cmbEmployee] on [frmPatientLookup]. If [cmbEmployee] is null than all records should be the result. If the choice is "Director" than only records that have "NA" should be filtered. If the choice is "RN" than the result should be all records with "RN", and if the choice is "Other" than the result should be null.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null as option in query (2002)

    Thanks, that is clearer. Try this WHERE part:

    WHERE ([Forms]![frmPatientLookup]![cmbReturn] Is Null) OR ([Forms]![frmPatientLookup]![cmbReturn] = "Director" AND FieldName = "NA") OR ([Forms]![frmPatientLookup]![cmbReturn] = "RN" AND FieldName = "RN") OR ([Forms]![frmPatientLookup]![cmbReturn] = "Other" AND FieldName Is Null)

    Replace FieldName with the name of the field you want to filter on.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null as option in query (2002)

    Wow!!! I've never done a query this way before and it opens up a whole world of new opportunities!!

    Thanks so much. It's perfect!!

    Leesha

Posting Permissions

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