Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727

    Query Criteria

    I have a query that I am trying to type this into the criteria field in design view:

    Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*"



    The problem is that if the txtCustomer field is blank on the form that opens a split form that is hooked up to this query, it doesn't show all the records. Some of the records do not exist with this criteria filled into the query and even if I type in the criteria to search for the record that is not showing in the split form, it won't be able to find it. It's like this criteria hides certain records from existence. If I delete the criteria in the design view query, all records show but now I can't search because it's no longer looking for a value in the txtCustomer field.

    Please help. Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is probably because your field is null

    try

    Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*" OR [Forms]![Counter Partial Criteria].[txtCustomer] is Null

    Alternatively

    Nz(CustomerName,"")
    Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*

  3. #3
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Thanks for the suggestions. First I tried this in the criteria field in the query in design view:

    Nz(CustomerName,"") Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*

    Had to close it with a quotation mark at the end as it would not accept it. This did nothing. If I click the filter button with all my unbound text boxes null it shows all 59k records which is great but the moment I go back and try to type in the customer name and click the filter button the split form opens with no records showing.

    Then I tried your first suggestion:

    Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*" OR [Forms]![Counter Partial Criteria].[txtCustomer] is Null

    This did basically the same thing. Then I tried just this:

    Forms]![Counter Partial Criteria].[txtCustomer] is Null

    Which didn't make sense to me as it's basically just saying that the txtCustomer field is null so I didn't expect that to work and it didn't. lol

    Am I doing something wrong with your code?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry missed the last "

    in the immediate window for the 'nz' example

    ?nz(null,"") like "*" & null & "*"
    True


    ?nz("Abcd","") like "*" & null & "*"
    True


    ?nz("Abcd","") like "*" & "ABC" & "*"
    True


    ?nz("def","") like "*" & "ABC" & "*"
    False

    so the first two will return all records
    then 3rd will return just those records with ABC in there somewhere
    and the 4th will not return any records that do not contain ABC

    Same for the other example (i've used the isnull function since vba doesn't use sql's '... is null')
    ?null like "*" & null & "*" OR ISNULL(NULL)
    True


    ?"Abcd" like "*" & null & "*" OR ISNULL(NULL)
    True


    ?"Abcd" like "*" & "ABC" & "*" OR ISNULL("ABC")
    True


    ?"def" like "*" & "ABC" & "*" OR ISNULL("ABC")
    False

    So think you are doing something wrong with the code, or there are other criteria you have not mentioned

    Suggest post some example data, the full sql you are using and the result you get from that example data

  5. #5
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Code:
    [CustomerName] Like "*" & Nz([Forms]![Counter Partial Criteria].[txtCustomer],"") & "*"

  6. #6
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CarlettoFed View Post
    Code:
    [CustomerName] Like "*" & Nz([Forms]![Counter Partial Criteria].[txtCustomer],"") & "*"
    Thanks for the suggestion. Tried this and added it to the Counter Partial Criteria_query for the Customer Name field under criteria. Then when went to open the Counter Partial Criteria form and this little tiny pop up came up saying to Enter Parameter Value and says CustomerName with a field for me to type in under it. So I tested it and typed john but it didn't filter anything. It popped up twice btw. So then I went back in the Query to remove it and the criteria field was blank but it created another field in the query called CustomerName with a new criteria in it. It's like it converted it to this:

    Like "*" & Nz([Forms]![Counter Partial Criteria].[txtCustomer],"") & "*"

    I copied it and deleted that new CustomerName field and pasted the code into the original Customer Name and then it worked. However, I then noticed that it started to remove records like my initial problem that I mentioned in my original post. So back to square one.

    It's odd because I just added this for my clerk initials field:

    Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitials] & "*"

    And this for my date field:

    Like "*" & [Forms]![Counter Partial Criteria].[txtDate] & "*"

    And everything is working fine for those 2 fields. If I type something into those fields in my form then click the button to take me to the split form, it will show the records that match that criteria. If I don't type anything into it, it will show all 59,291 records.

    When I add this to the customer name field in the query and do the same thing, it will show the data in the split form that matches the customer name I typed into the form but if I leave it blank and click the button that opens the split form then it will show something like 59,215 records so I know not all of the records are showing. I figured out a record that was missing just to test if I could look it up and I couldn't so it's like if I add criteria in the query for Customer Name, it will hide some records for some reason. Do you know what this is happening?

  7. #7
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CJ_London View Post
    sorry missed the last "

    in the immediate window for the 'nz' example

    ?nz(null,"") like "*" & null & "*"
    True


    ?nz("Abcd","") like "*" & null & "*"
    True


    ?nz("Abcd","") like "*" & "ABC" & "*"
    True


    ?nz("def","") like "*" & "ABC" & "*"
    False

    so the first two will return all records
    then 3rd will return just those records with ABC in there somewhere
    and the 4th will not return any records that do not contain ABC

    Same for the other example (i've used the isnull function since vba doesn't use sql's '... is null')
    ?null like "*" & null & "*" OR ISNULL(NULL)
    True


    ?"Abcd" like "*" & null & "*" OR ISNULL(NULL)
    True


    ?"Abcd" like "*" & "ABC" & "*" OR ISNULL("ABC")
    True


    ?"def" like "*" & "ABC" & "*" OR ISNULL("ABC")
    False

    So think you are doing something wrong with the code, or there are other criteria you have not mentioned

    Suggest post some example data, the full sql you are using and the result you get from that example data

    Yes before I start testing your lines of code I think I should explain more in detail of my setup.

    So I have a form called Counter Partial Criteria. It's record source is called Counter Partial Criteria_query. The query is where I have some of the criteria fields filled in such as Date and Clerk Initials with this:

    Like "*" & [Forms]![Counter Partial Criteria].[txtDate] & "*"
    Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitials] & "*"

    In the Counter Partial Criteria form I have 5 unbound text boxes with a button that will open a split form called Counter Partial Criteria Results. Right now I only have 2 of the 5 unbound text boxes working like mentioned above. The Date and Clerk Initials. So I eventually want to setup the other 3 text boxes which I am trying to do now with Customer Name. Anyway, this split form will open showing the records based off of what the user typed into the Counter Partial Criteria with the 5 unbound text boxes. If these text boxes are not filled in and you click the button to open the split form, no filter should be applied and all records should show which it does, 59,291 records. However, the moment I add this for the Customer Name criteria in the query:

    Like "*" & [Forms]![Counter Partial Criteria].[txtCustomer] & "*"

    and leave the 5 text boxes blank in the Counter Partial Criteria form and click the button to open the split form, it will show something like 59,215 records. I then tried to figure out at least one record that was missing when this happened and went back to the 5 text box form and typed in criteria that should allow me to see that record when I click the button that opens the split form and it could not produce that record which tells me that record is gone or hidden from viewing based on that criteria I put in the query for Customer Name.

    So that is the problem I am having right now. Do you know what is causing this or what I could do to fix this?



  8. #8
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Perhaps if you attach an example file it will be easier and quicker to show you how to do it.

  9. #9
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CarlettoFed View Post
    Perhaps if you attach an example file it will be easier and quicker to show you how to do it.
    Sure. I can make a frontend file that just shows the query and forms that I am trying to work with but did you also need the backend structure? Also how do I upload it to this site?

  10. #10
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CarlettoFed View Post
    Perhaps if you attach an example file it will be easier and quicker to show you how to do it.
    Ok file is attached.
    Attached Files Attached Files

  11. #11
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    It takes a minimum of data to be able to verify the operation.

  12. #12
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Canceled the message.

  13. #13
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CarlettoFed View Post
    It takes a minimum of data to be able to verify the operation.
    The data is confidential. I just tested the Database I uploaded and added about 4 generic records to it and it doesn't even load those records into the Counter Partial Criteria Results split form. I'm going to remove the query critieria and see if that helps.

  14. #14
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Here's the example, but it looks like I need to read a bit more about how to use Access.
    Attached Files Attached Files

  15. #15
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by CarlettoFed View Post
    It takes a minimum of data to be able to verify the operation.
    Oh it did help. I removed the criteria in the query on the date and clerk and now it shows all 4 records. So this is what we need to figure out. Why is this happening?

    I'll upload this new database for you to try with the 4 records.

    Dang it, now the database is too large for this site. With the 4 records it jumped to 512kb lol. 500kb is the limit.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-25-2020, 10:39 AM
  2. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  5. Replies: 1
    Last Post: 07-13-2011, 11:00 AM

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