Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12

    Max records with wildcard selection?

    Hi,
    Apologies if this has been asked before, but I've searched the forums and can't find anything.

    Question is:


    Is there a limit to the number of rows a wildcard selection can retrieve?

    I have a large table >10000 rows.
    If I write a simple query:
    Select count(fieldName) Where fieldName like 'N?*'

    This works as expected until there are more than 7502 records beginning with an 'N' then the query returns 0
    If I change the where clause to be 'N*' then it seems to work even if there are more than 7502 records to return...

    Any ideas please?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I've never used the ? wildcard character. Really makes no sense to use with * wildcard.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hi June, Thanks for your reply. The reason I want to use the '?' Is that it needs to match at least 3 characters starting with an 'N'.
    'N*' would match (eg.) 'N1' whereas 'N?*' will not. In my query, I don't want values such as 'N1' to be included...
    However, I don't really want this thread to get diverted.
    The question was about whether I'm hitting some sort of limit in Access at 7502 records matched.
    Thanks, Chris

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Since 'N*' returns more than 7502 records I am inclined to think something else is happening.

    I don't really follow your explanation for use of ? wildcard. I tested:

    "N1NN" LIKE "N?*"

    "NaNN" LIKE "N?*"

    Both return True.


    Perhaps if you showed example raw dataset and desired output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hi June,
    Take the value N1
    N* would return TRUE
    N?* would return FALSE
    This is the behaviour I require.
    Forgetting this requirement for a minute though when the query returns more than 7502 records(which I expect it to) it fails when I have the '?' wildcard character included but succeeds without it. In production though I can't leave the '?' out as it will include unwanted records.
    I will see what I can do about posting an example, but that may take a few days.

    Thanks for your help.

    Chris.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Test in VBA editor immediate window:

    ?"N1" LIKE "N?*"
    True

    ?"N1" LIKE "N[!0-9]*"
    False
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hmm, maybe I need to check more carefully exactly what it's trying to do. I've inherited this application from someone else, and I've never had to work with this bit before. The solution may be to change how the criteria works. As your post implies.
    I'm still curious as to why it stopped working when we hit 7502 records selected though!

  8. #8
    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
    Chris,

    I don't think the number of records is your issue.
    What exactly are the characters to match --you said starting with "N", but what are the next 3?
    Any character, only alpha, only numeric???
    Please show us an example.

    You could try something along Left(someField,4) like "N"&"your criteria here".
    But an example will help clarify the requirement.

  9. #9
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Orange,
    I'm not sure. All I really know is that the query WAS working fine. Then it stopped.
    The table it works on is continually growing.
    If I delete a few records so that the number of records the query returns is less than 7502 then it works again, but as soon as I add records to make the total higher than 7502 then it stops working again. It is very strange.
    This is part of quite a big complicated database, but I'll try to extract the table in question and post a sample as soon as I can
    Thanks again, Chris.

  10. #10
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hi,

    Ok.

    Here is an example extracted from my database.

    There is one query and one table.

    As it stands, the query will return 0

    If you go to the table, and change one row with a QuoteNumber beginning with an 'N' so that it begins with a different letter then the query should work correctly and return 7502. Go back to the table and change the QuoteNumber back so that is begins with an 'N' again. Rerun the query, and it should again incorrectly return 0
    If you change the query so that it says like 'n*' rather than like 'n?*' then it seems to work regardless of the number of records being returned.

    Note1 I'm using Access 2016 on my computer, but the database is saved as an .MDB file for compatibility reasons. Converting to a newer style of Access database file is a lot of work and is not practical at this time. Having said that, I tried saving this example as an ACCDB file, and it still seems to show the same problem.
    Note2 Further to June's earlier comment, it looks like 'N?*' and 'N*' are functionally the same so it seems as if my workaround is just to change the query to use the second version and deal with two character codes like 'N1' or 'NA' seperately. However, I'd still be really interested in hearing if anyone knows why this is not working as it stands.
    Note3 I've attached the mdb as a ZIP file as it was otherwise too large to be allowed.

    EDIT - Note4 - I've just noticed that if I change the WHERE to be something like 'N[0-9]*' I still get the same problem

    Many thanks for everyone's input.

    Chris.
    Attached Files Attached Files

  11. #11
    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
    You have 14162 records. When I grouped by the left most 2 chars, here are the counts.

    SQL
    Code:
    SELECT Count(PropEstimates.QuoteNumber) AS CountOfQuoteNumber, Left([QuoteNumber],2) AS Expr1
    FROM PropEstimates
    GROUP BY Left([QuoteNumber],2);
    Code:
    CountOfQuoteNumber Expr1
    3 A0
    32 E0
    6547 E1
    2 M0
    27 M1
    7503 N0
    47 W0
    1 XE

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You earlier stated you don't want values such as "N1*" to be included, yet the "N?*" parameter should retrieve only records beginning with N.

    I removed the primary key designation from the table. Now the query returns 7503. More mystery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    June, You are correct. It seems like I was incorrect initially. I'm not sure why the where clause even has the '?' in it. It seems to be completely obsolete. As far as my actual database is concerned, I'm going to have to do some work to find out why the ? was ever included in the first place. It may well be it was an error. I think the special cases must be dealt with elsewhere.
    However, that is something I think I can deal with, and is outside the scope of this discussion although your input is appreciated.
    The question remains though why does the query return zero when it should not??

    Cheers,
    Chris.

  14. #14
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hi Orange,
    Interesting to see those stats, but sorry, I'm missing your point(?).

    Thanks,
    Chris.

  15. #15
    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
    My real point is what exactly is the query suppose to be looking for? You need to identify the business facts involved. Only a 0 follows an N in you test data???
    The ? is used to find a matching single character in a position. The * accepts any character for any length.

    see this for more info

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

Similar Threads

  1. Replies: 2
    Last Post: 10-29-2015, 12:38 PM
  2. Replies: 9
    Last Post: 12-05-2013, 11:48 AM
  3. Listbox selection retains into following records.
    By CregoryJames in forum Forms
    Replies: 3
    Last Post: 10-15-2012, 10:00 AM
  4. Filtering/Querying Records using Selection Screen
    By Jonny333 in forum Programming
    Replies: 1
    Last Post: 12-22-2011, 08:56 PM
  5. Replies: 3
    Last Post: 07-31-2010, 07:41 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