SELECT problem with apostrophies

BPak · #1 07-04-2009, 01:11 PM

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?

RuralGuy · #2 07-04-2009, 04:43 PM

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 "+".

BPak · #3 07-04-2009, 06:36 PM

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.

Quote:

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

RuralGuy · #4 07-04-2009, 07:12 PM

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?

BPak · #5 07-05-2009, 07:41 PM

Quote:

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.

RuralGuy · #6 07-05-2009, 07:59 PM

Thanks for posting back. At least you have found a solution for your issue. BTW, what software are you using if not Access?

BPak · #7 07-05-2009, 10:16 PM

I am using Emergence BASIC.

RuralGuy · #8 07-06-2009, 05:22 AM

Thanks for responding.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Script to run select query	pushpm	Programming	2	05-06-2009 06:36 AM
Select query with two criteria	corni	Queries	1	01-22-2009 02:23 AM
very difficult (for me!) SELECT query	igorbaldacci	Queries	1	12-02-2008 12:30 PM
Combo box select from two fields	cnestg8r	Access	0	10-31-2008 08:05 AM
SELECT FUNCTION help please	scott munkirs	Queries	0	10-17-2006 05:44 AM