Problem of pesky apostrophe when SQL runs. 
Author Message
 Problem of pesky apostrophe when SQL runs.

Using Win95, Office 97 and DAO3.5 and new to Access/VBA.
I have four Tables in my database and four queries.
I generate a series of temporary queries, one for each table and pass the
recordset to generate the next query.
Each recordset plus field names is copied to a spreadsheet.
Everything ran smoothly and speedily thanks in no small measure to the help
of this newsgroup and briefly gave me a warm inner glow.......EXCEPT when
the last query qdf4.Sql is running and it strikes any Name that contains an
apostrophe in the recordset rst3!Name whereupon warm inner glow replaced by
cold outer chill!
In this case a syntax error is reported which I can well appreciate since
this pesky apostrophe is confounding the single quotes around the relevant
part of the :
WHERE TABLE4.Name='" & rst3!Name & "'"
Note in case this syntax is unclear: read as: single quote-double
quote-space-&-space-rst3!Name-space-double quote-single quote-double quote.
Given the fairly frequent occurrence of apostrophies in my Name field is it
possible to work around this problem by changing my code?
Any help appreciated!
Am I forced to replace all apostrohies in my database tables with say a wild
card?
(Not overly keen on this since approx 200K records).
Can this be done with a query and if so how?
I thought of the method of copying the temporary recordset to Excel,
applying a Find & Replace routine to remove all apostrophies then appending
this back to the original table and re-querying it but this seems
cumbersome, ugly and slow.
Anyone have thoughts on this?
With the(hopeful!) added indulgence of the group for this newbie: At the
moment I enter the Date value manually in three places but would like to do
this programmatically either lifting it from a specific cell in my
spreadsheet or via a dialogue box.
Do I need to learn about Parameter queries to do this?
Any thoughts on how to proceed with this appreciated!
Cheers, PeterJ.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Relevant code below.
(Earlier code has Dims and generates qdf1, rst1, qdf2, rst2 from TABLE1 and
TABLE2)
''''''''''''''''''''''''''''''''''''''''''''''''''
Set qdf3 = dbsCurrent.CreateQueryDef("")  'Create a temp query.
Do While Not rst1.EOF
    qdf3.Sql = "SELECT * FROM TABLE3 WHERE TABLE3.Date=#21/5/2000# _
And TABLE3.TCode='" & rst2!TCode & "'"
Set rst3 = qdf3.OpenRecordset()
         rst3.MoveFirst
        Do While Not rst3.EOF
                Set qdf4 = dbsCurrent.CreateQueryDef("") 'Create a temp
query.
                qdf4.Sql = "SELECT * FROM TABLE4 WHERE TABLE4.Name='" & _
rst3!Name & "'"
            'Copy the recordset to the spreadsheet.
            Cells(2, 1).CopyFromRecordset rst4
            rst3.MoveNext
 Loop


Sun, 10 Nov 2002 03:00:00 GMT  
 Problem of pesky apostrophe when SQL runs.
Try using either

   ... WHERE TABLE4.Name=" & Chr$(34) & rst3!Name & Chr$(34)

or
   ... WHERE TABLE4.Name=""" & rst3!Name & ""

(In the second case, the first one is 3 double quotes in a row, the last one
is 2 double quotes)

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


Quote:
> Using Win95, Office 97 and DAO3.5 and new to Access/VBA.
> I have four Tables in my database and four queries.
> I generate a series of temporary queries, one for each table and pass the
> recordset to generate the next query.
> Each recordset plus field names is copied to a spreadsheet.
> Everything ran smoothly and speedily thanks in no small measure to the
help
> of this newsgroup and briefly gave me a warm inner glow.......EXCEPT when
> the last query qdf4.Sql is running and it strikes any Name that contains
an
> apostrophe in the recordset rst3!Name whereupon warm inner glow replaced
by
> cold outer chill!
> In this case a syntax error is reported which I can well appreciate since
> this pesky apostrophe is confounding the single quotes around the relevant
> part of the :
> WHERE TABLE4.Name='" & rst3!Name & "'"
> Note in case this syntax is unclear: read as: single quote-double
> quote-space-&-space-rst3!Name-space-double quote-single quote-double
quote.
> Given the fairly frequent occurrence of apostrophies in my Name field is
it
> possible to work around this problem by changing my code?
> Any help appreciated!
> Am I forced to replace all apostrohies in my database tables with say a
wild
> card?
> (Not overly keen on this since approx 200K records).
> Can this be done with a query and if so how?
> I thought of the method of copying the temporary recordset to Excel,
> applying a Find & Replace routine to remove all apostrophies then
appending
> this back to the original table and re-querying it but this seems
> cumbersome, ugly and slow.
> Anyone have thoughts on this?
> With the(hopeful!) added indulgence of the group for this newbie: At the
> moment I enter the Date value manually in three places but would like to
do
> this programmatically either lifting it from a specific cell in my
> spreadsheet or via a dialogue box.
> Do I need to learn about Parameter queries to do this?
> Any thoughts on how to proceed with this appreciated!
> Cheers, PeterJ.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Relevant code below.
> (Earlier code has Dims and generates qdf1, rst1, qdf2, rst2 from TABLE1
and
> TABLE2)
> ''''''''''''''''''''''''''''''''''''''''''''''''''
> Set qdf3 = dbsCurrent.CreateQueryDef("")  'Create a temp query.
> Do While Not rst1.EOF
>     qdf3.Sql = "SELECT * FROM TABLE3 WHERE TABLE3.Date=#21/5/2000# _
> And TABLE3.TCode='" & rst2!TCode & "'"
> Set rst3 = qdf3.OpenRecordset()
>          rst3.MoveFirst
>         Do While Not rst3.EOF
>                 Set qdf4 = dbsCurrent.CreateQueryDef("") 'Create a temp
> query.
>                 qdf4.Sql = "SELECT * FROM TABLE4 WHERE TABLE4.Name='" & _
> rst3!Name & "'"
>             'Copy the recordset to the spreadsheet.
>             Cells(2, 1).CopyFromRecordset rst4
>             rst3.MoveNext
>  Loop



Sun, 10 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Pesky SQL problems --help please

2. SQL, VBScript and apostrophe problem

3. SQL Query Problem - apostrophe

4. When is an apostrophe not an apostrophe ?

5. How to handle apostrophes in SQL

6. SQL - Including strings contain an apostrophe

7. How to handle apostrophes in SQL

8. Apostrophe in SQL

9. Sql and apostrophes

10. Using single ' (apostrophe) in a SQL query

11. Dealing with an apostrophe in SQL

12. SQL and apostrophe errors

 

 
Powered by phpBB® Forum Software