Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815

    Orange, core issue has been that the ? wildcard criteria works as long as there are no more than 7502 records that meet the criteria. The 7503rd record causes the query count to be 0.

    To compound the mystery, when I removed the PK setting, the 7503 count returns.
    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.

  2. #17
    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
    That may all be true, buit I'd like to know what the code is looking for.
    If you want a "c" in position 3 of a string, you could look for
    yourfield like "N?c*"

  3. #18
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Just to be clear, the table and query in my example are both reduced in complexity to show the root problem which is the fact that over a certain number of records, the query appears to fail.
    As I mentioned before, I have inherited this database, and have never had to look at this part of it before as it has always just worked...

    However the following is my latest info on what is going on.
    Essentially, the field it is looking at contains job numbers which are a letter followed by numerical digits. The numerical suffix is sequential, increasing by 1 for each new job. The letter at the beginning identifies the type of job. The ? wildcard was supposed to ignore special codes which were intended to be only 2 characters long. However this was never properly implemented, and there are no 2 character codes in use. (which is lucky as they would not have been properly dealt with anyway!). Hence the business solution to my problem is to simply remove the ? wildcard and just leave the * Problem Solved

    However this doesn't alter the fact that something weird is going on, and the query as per my example database is not working as it should. I would like to find out what's going on just to satisfy my curiosity really.
    Hope that makes sense.

    Thanks,

    Chris

  4. #19
    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 agree, it would be good to know what the code was intended to do.
    If there was an "N" followed by a 2 char code, then something, you would have something like
    "N??*" where the ?? represents 2 chars.
    Last edited by orange; 02-18-2018 at 11:18 AM.

  5. #20
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Guys,
    This is what I was worried about...
    Please don't get too hung up on the specifics of the query.
    The example is not even the production version of the query. It's a simplified version that demonstrates the problem with Access not working as expected with more than a certain number of records.
    I'm fairly confident that I can fix the query in my production database but I have no idea why this demo query is not functioning correctly as described in my earlier posts. That is the point of this thread IMO and is what I would appreciate replies and comments on. (Not that I do not also appreciate the help and suggestions offered this far)
    Many thanks,
    Chris.

  6. #21
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Chris

    I understand this is now really just curiosity on your part.
    However, I think you should accept that using ? followed by * is incorrect in this context and therefore the results cannot be guaranteed.
    There seems to be no obvious reason why 7503 triggers the incorrect count behaviour but ....it does.
    Accept that it doesn't matter for your purposes as the wildcard shouldn't use both characters ... and move on.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #22
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hi Ridders,
    You are correct in that I don't need a solution to this to fix my database right now. However although I accept that '?*' as a wildcard combination is essentially meaningless, various other wildcard solutions that DO make sense also exhibit the same faulty behaviour. For example combinations of [] and * (eg 'N[0-9]*' ). So it could present a genuine problem under certain circumstances.
    That said, as I mentioned, I don't need a solution to this just now, and no one seems to know the reason anyway, so it is probably best, as you say, to move on...

    Best regards,
    Chris

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    All I can offer is that it has something to do with the primary key indexing because when I remove the PK setting, the query returns 7503.
    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.

  9. #24
    chrismay is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    12
    Hmmm, yes. Thank you for that. I'll have more of a play with this when I have some more time. I'll update this thread if I find anything useful/interesting

    Cheers,

    Chris.

Page 2 of 2 FirstFirst 12
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