Results 1 to 8 of 8
  1. #1
    BPak is offline Novice
    Windows XP Access 97
    Join Date
    Jul 2009
    Posts
    4

    SELECT problem with apostrophies

    I have a SQL written like this:

    TheName = Bash'n'Crash

    "SELECT * FROM Clients WHERE ClientName = '" + TheName + "'"

    There are two apostrophies in the query and Access does not find the records.

    If I only have one apostrophy in the word like in the following then it will work:

    TheName = O'Brien

    Changed TheName to read as O''Brien with two apostrophies between the O and Brien.

    "SELECT * FROM Clients WHERE ClientName = '" + TheName + "'"



    I have tried putting two apostrophies in the Bash''n''Crash name but it does not work.

    Any help please?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Leave TheName alone and change your delimiter to a double quote:
    "SELECT * FROM Clients WHERE ClientName = """ & TheName & """"
    That is 3 at the beginning and 4 at the end. You shoud also use the "&" as the string concantenation character rather than the "+".

  3. #3
    BPak is offline Novice
    Windows XP Access 97
    Join Date
    Jul 2009
    Posts
    4
    Thanks Rural Guy.

    I cannot use the & instead of the + in the software language I am using. The + is an operator that appends two strings together.

    It looks like you are saying surround TheName with double Quotes in the select Statement rather than with apostrophy.

    EDIT

    Ok - I tried that but it did not work. NO records were found in the SELECT statement.

    Here is the SELECT etc.

    FrmSql = "SELECT FrmID, HorseName, BigDate, RDate, Fin, NRns, BMgn, Meet, RNO, Dist, Event, RClass, Age, Rest, RcDol, WonDol, TC, Jockey, "
    FrmSql += "Bar, WtCd, Allow, MinWt, RTime, SDist, Sect, Odds, "
    FrmSql += "INSD, IN12, IN8, INTn, F1, Winner, Wgt1 FROM F20090703 WHERE HorseName = " + CHR$(34) + HorseName + CHR$(34)
    FrmSql += " ORDER BY BigDate DESC"
    hFe = dbExecSQL(pdb,FrmSql)
    if hFe <> NULL
    WHILE dbGet(hFe) <> FALSE
    dbGetData(hFe,1,FrmID)
    dbGetData(hFe,2,FHorseName)
    I have done more testing with the double apostrophies and now have it working OK by placing Two Apostriphies in the Name used in the WHERE clause.

    BPak

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    When you used the code you posted was the horses name Bash'n'Crash with just single ticks around the 'n' and no records were returned; or had you modified the name at that point?

  5. #5
    BPak is offline Novice
    Windows XP Access 97
    Join Date
    Jul 2009
    Posts
    4
    When you used the code you posted was the horses name Bash'n'Crash with just single ticks around the 'n' and no records were returned; or had you modified the name at that point?
    In the above code where I used the CHR$(34) the name was Bash'n'Crash and had not been modified.

    Matter of fact NO records were returned for any of the horses when I used that code. Double quotes were not accepted in the query.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for posting back. At least you have found a solution for your issue. BTW, what software are you using if not Access?

  7. #7
    BPak is offline Novice
    Windows XP Access 97
    Join Date
    Jul 2009
    Posts
    4
    I am using Emergence BASIC.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for responding.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Script to run select query
    By pushpm in forum Programming
    Replies: 2
    Last Post: 05-06-2009, 08:36 AM
  2. Select query with two criteria
    By corni in forum Queries
    Replies: 1
    Last Post: 01-22-2009, 05:23 AM
  3. very difficult (for me!) SELECT query
    By igorbaldacci in forum Queries
    Replies: 1
    Last Post: 12-02-2008, 03:30 PM
  4. Combo box select from two fields
    By cnestg8r in forum Access
    Replies: 0
    Last Post: 10-31-2008, 10:05 AM
  5. SELECT FUNCTION help please
    By scott munkirs in forum Queries
    Replies: 0
    Last Post: 10-17-2006, 07:44 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