Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2008
    Posts
    13

    Unanswered: Ampersand problem in sql query

    Hello all,

    I have an Access 97 database with a field for customer numbers. Some of the customer numbers have ampersands (i.e. A &374 there is a space between the A and the ampersand) and some don't.
    When run a sql query such as:

    ssql = "SELECT * FROM TABLE1 Where TABLE1.CNUMB = ' " & stCNUMB & " '

    it will return customer numbers that don't have the ampersand in the name, such as A.J345 but it wont return A &374.

    Is there a work around for this issue.

    Any help would be greatly appreciated.
    Thank You in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, it will work correctly with no workaround necessary.

    Did you copy paste that code, because there's a spurious space after your first apostrophe!

    Before you execute the code you think is failing - do a Debug.Print of your ssql variable and post back the "offending" SQL statement here.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There is also a suspicious missing double quote [not] at the end of the SQL statement, along with another suspicious space.

    ssql = "SELECT * FROM TABLE1 Where TABLE1.CNUMB = '" & stCNUMB & "'"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2008
    Posts
    13
    Hi Georgeev and thanks for your quick reply.

    I believe the code syntax is correct.

    I did a debug.print on the ssql variable and here is what it returned:

    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = '811 ' and PRBREAKS.PTNUMB = '205-060-918-001C' And CStr(PRBREAKS.DATE) = '9/13/2006' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A &374 ' and PRBREAKS.PTNUMB = 'HMCRM-2' And CStr(PRBREAKS.DATE) = ' 1/2/1993 ' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A &538 ' and PRBREAKS.PTNUMB = 'HMCRM-2' And CStr(PRBREAKS.DATE) = ' 1/2/1997 ' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A &538 ' and PRBREAKS.PTNUMB = 'HMCRM-3' And CStr(PRBREAKS.DATE) = ' 2/12/1999' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A &538 ' and PRBREAKS.PTNUMB = 'HMCRM-3' And CStr(PRBREAKS.DATE) = ' 1/3/2000 ' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A-U341 ' and PRBREAKS.PTNUMB = '3-51208-1C' And CStr(PRBREAKS.DATE) = '6/16/1992' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A.J723 ' and PRBREAKS.PTNUMB = '204-001-362-001C' And CStr(PRBREAKS.DATE) = '5/12/2006' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A.J723 ' and PRBREAKS.PTNUMB = '205-030-236-005C' And CStr(PRBREAKS.DATE) = '6/6/2001' ;
    SELECT * FROM PRBREAKS Where PRBREAKS.CNUMB = 'A.J723 ' and PRBREAKS.PTNUMB = '206-040-241-001C' And CStr(PRBREAKS.DATE) = '8/16/2006' ;

    It appears that the SELECT statement is running OK. When I do a MsgBox(rstItems!CNUMB), it displays the first CNUMB but not the next four (with the ampersand), it then displays the rest of the CNUMB's. What it appears to be doing is not finding the CNUMB with the ampersands in the database as a match with the SELECT statement.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Look closer. This has NOTHING to do with ampersands.

    How many dates start with a space after being converted to a string?

    Would have been easier if you had pasted the entire SQL, not just the bit of it that you think is failing!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *Round of applause to ST*
    Well spotted my friend!
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Posts
    13
    Wow Star Trekker, your pretty sharp.

    I'll correct the date issue and let you know how it goes.

    Thanks a bunch

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Where is my dancing banana smilie!!!



    You're welcome

    Ah, here's the banana!
    Attached Thumbnails Attached Thumbnails banana.gif  
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    This site needs more smilies dammit!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jun 2008
    Posts
    13

    Talking

    Well, I got rid of the leading space before the date and guess what?

    That's right it works great.

    Thanks for the help, now I can move on.

    And here's a smile.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good stuff; glad you got it sorted out
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    This site needs more smilies dammit!!
    Suggestions & Feedback
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    This site needs more smilies dammit!!
    Will a simile do?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nup!

    Suggestion topic created

    http://www.dbforums.com/showthread.php?t=1632327

    Go register your support for more smilies!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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