Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Curious about query results (Acc97 SR1)

    Hi Gurus,

    This is not a problem, a colleague asked me a question and I've got no idea.

    He was running a query on a customer database, and decided to split the results into two reports, an A-L report and an M-Z report.

    The criteria he used to create the A-L report on the Customer Name field was: <"M*"

    This worked fine, with one exception. There was a customer called M&M Incorporated, which showed up in the A-L list, and not in the M-Z list.

    I told him it was bviously something to do with the ampersand in the customers name, and to resolve the problem, I just created a non-displaying field in the query that returned the first character in the customer field and set the criteria to <"M".

    The workaround worked fine, but then he asked me "why did the first one not work" and I couldn't think of an answer beyond the ampersand caused it, for whatever reason.

    Any takers on the reason?

    Regards,
    JP

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Curious about query results (Acc97 SR1)

    Hi,
    My guess would be that it is related to the ASCII value for '*'. The ASCII value for '&' is less than that for '*' (as is the value for '%', '$' and various others) and I suspect that somewhere in the code that interprets '*' as a wildcard, the ASCII values are calculated and used. You would probably also find that it returned 'M' if that existed as a customer name, since that is technically less than 'M' followed by any combination. You could probably use <="L*" as the criteria to get what you wanted.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curious about query results (Acc97 SR1)

    Thanks, mate - makes sense

Posting Permissions

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