Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Unanswered: Filter Query Help: SQL

    Hey everyone,

    So I have a form with a large listbox on it and with the source being a query of an equipment table. The end user will have the option to filter each of these fields using textboxes above the listbox. The idea is for these textboxes to work as filters as any normal filter would work. The SQL I have seems work fine, except for when a field is null in my original table. I used star wildcards to try and account for this, but for whatever reason, it's not working. Please let me know if you need anymore clairfication on something. Any advise and/or suggestions are greatly appreciated. Feel free to hack away at the code as much as you see fit

    Code:
    SELECT tblEquipment.EquipmentID, tblCampusList.Campus, tblBuildingList.Building, tblRoomList.Room, tblLabList.Lab, tblManufacturerList.Manufacturer, tblEquipment.EquipmentName AS [Equipment Name], tblEquipment.Nickname, tblEquipment.SerialNumber AS [Serial Number], tblEquipment.EquipmentNumber AS [Equipment Number], tblEquipment.ModelNumber AS [Model Number], tblUserList.LastName AS [Last Name], tblStatusList.Status
    FROM tblUserList INNER JOIN (tblStatusList INNER JOIN ((tblCampusList INNER JOIN (tblBuildingList INNER JOIN tblRoomList ON tblBuildingList.BuildingID = tblRoomList.BuildingID) ON tblCampusList.CampusID = tblBuildingList.CampusID) INNER JOIN (tblManufacturerList INNER JOIN (tblLabList INNER JOIN tblEquipment ON tblLabList.LabID = tblEquipment.LabID) ON tblManufacturerList.ManufacturerID = tblEquipment.ManufacturerID) ON tblRoomList.RoomID = tblEquipment.RoomID) ON tblStatusList.StatusID = tblEquipment.StatusID) ON tblUserList.UserKey = tblEquipment.OwnerID
    WHERE (((tblCampusList.Campus) Like "*" & [Forms]![frmEquipmentSelection]![txtCampus] & "*") AND ((tblBuildingList.Building) Like "*" & [Forms]![frmEquipmentSelection]![txtBuilding] & "*") AND ((tblRoomList.Room) Like "*" & [Forms]![frmEquipmentSelection]![txtRoom] & "*") AND ((tblLabList.Lab) Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*") AND ((tblManufacturerList.Manufacturer) Like "*" & [Forms]![frmEquipmentSelection]![txtManufacturer] & "*") AND ((tblEquipment.EquipmentName) Like "*" & [Forms]![frmEquipmentSelection]![txtEquipmentName] & "*") AND ((tblEquipment.Nickname) Like "*" & [Forms]![frmEquipmentSelection]![txtNickname] & "*") AND ((tblEquipment.SerialNumber) Like "*" & [Forms]![frmEquipmentSelection]![txtSerialNumber] & "*") AND ((tblEquipment.EquipmentNumber) Like "*" & [Forms]![frmEquipmentSelection]![txtEquipmentNumber] & "*") AND ((tblEquipment.ModelNumber) Like "*" & [Forms]![frmEquipmentSelection]![txtModelNumber] & "*") AND ((tblUserList.LastName) Like "*" & [Forms]![frmEquipmentSelection]![txtUser] & "*") AND ((tblStatusList.Status) Like "*" & [Forms]![frmEquipmentSelection]![txtStatus] & "*"));

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    For Nulls, you need to use the Nz function. Example:
    Nz([ID],0)
    HTH

  3. #3
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    How could that Nz() be applied to the current code though?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....either write the query n the fly and use that (or use the where clause as your filter)

    or use IIF to test if a supplied value is null or not

    eg:-

    select my, column, list from mytable

    where iif(isnull(acontrol),1=1, acolumn= acontrol), iif....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    You need to ad the Nz code to your query that the query is based on.

  6. #6
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Well, it's not an issue of the when the control is null, it's an issue of having a null value in a table. For example, if I don't enter a room for a piece of equipment, it won't come up in the equipment summary query because the table that lists the rooms and has the PK values for my rooms doesn't have a null option.

    I tried wrapping each "Like" in a Nz with no luck. For example:

    Code:
     Where (tblCampusList.Campus)=Nz((tblCampusList.Campus Like "*" & ...,"Not Found")
    There were also several other iterations... No luck

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    (room = '404' or isnull(room)) or
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    That doesn't do it either. If I remove the table with the null field from the query, then the record will show up again, but even if I search for just null records in the null field, it won't give me any results

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have ou tried 'thqt doesn't work'

    Code:
    (room = '404' or isnull(room))
    will return rows where the string/text column = '404' or is null
    if room was numeric
    Code:
    (room = 404 or isnull(room))
    what did you use to search for null values
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    I have tried:

    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*" Or IsNull([Lab])
    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*" Or IsNull("Lab")
    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*" Or IsNull([tblEquipment]![LabID])
    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*" & "Or IsNull([Lab])"
    Code:
    IsNull([Lab]) Or Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*"
    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtLab] & "*" Or Is Null



    I think it's because this field is on the "tblLab" table and that doesn't have a null field in it. In reality I want to say, if tblLab!Lab is like the textbox OR if tblEquipment!LabID is Null.

    Does that make sense?
    Last edited by pdevito3; 07-02-14 at 12:24.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Text literal values must be delimited by using a pair of ' or " as post #9 shows
    this sort of thing is tricky to diagnose by remote. You need to sse the actual values. So either assign the value of ths code to a variable and display it in a message box or better yet use a breakpoint and examine the code

    this approach would quickly identify what is going wrong as opposed to guessing or assuming

    do you know what the value of txtlab actually is.... not what you think it is but what the code is actually using
    have you forced declaration of all variables.... either use 'option explicit' at the start of each form, report or modules code. Modern versions of access allow you to set this as an option.

    What precedes the like.....
    StrFilter ="Lab like '*' & myvar & "*' or isnull(lab)"
    Last edited by healdem; 07-02-14 at 14:49.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Healdem, appreciate the response. While I would love to figure this out, I have found a "loophole" that isn't ideal, but it will work.

    What I decided to do was require all of the fields in question and added a "TBD" field for users to use if they are unsure what a value might be. This seemed to solve the problem, since there was never a problem when text was present.

    Regardless, thanks again for the repeated effort.

  13. #13
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    And one more note, I did find a solution for fields that didn't have a relationship between tables:

    Code:
    Like "*" & [Forms]![frmEquipmentSelection]![txtSerialNumber] & "*" Or Is Null

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Like "'*" & [Forms]![frmEquipmentSelection]![txtSerialNumber] & "*'" Or Is Null
    I could believe...
    one of the problems here is you use the query designer, and I don't. so I wouldn't know how to express what you want in the query designer criteria... its too limiting and too restrictive.

    If you are serious about using Access for development then do yourself a favour and learn SQL (thats all the front end does is create SQL in the background)

    by definition the query designer can never EVER be as effective producing SQL as someone who knows how to use SQL

    antoher point to learn from this is learn how to use the debugger
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Agreed! I know a bit, but not enough for something this advanced. I'm working on it! Haha

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
  •