I'm having a problem with a 'special' character in a query 
Author Message
 I'm having a problem with a 'special' character in a query

    Set dbs = CurrentDb()
    strSQL = "SELECT * FROM customer"
    strSQL = strSQL & " WHERE customer.dealer='"& me!incident_number &  "';"
    Set rst = dbs.OpenRecordset(strSQL)

    My problem is that the variable 'incident_number' is system generated
from another application and within the string type variable are ASCII text
characters that are causing my query to crash.

    The character is a ' (single quote).  Now within the code above the
query has to have a single quote to distinquish a text variable from the
remaining query.

    How can I get around is this issue?

Scott



Mon, 05 Dec 2005 23:24:51 GMT  
 I'm having a problem with a 'special' character in a query
Instead of using the single quote, you can use a pair of double quotes. Try
this:

Set dbs = CurrentDb()
strSQL = "SELECT * FROM customer"
strSQL = strSQL & " WHERE customer.dealer="""& me!incident_number  &  """;"
Set rst = dbs.OpenRecordset(strSQL)

or use the chr function to return the double quote character:

Set dbs = CurrentDb()
strSQL = "SELECT * FROM customer"
strSQL = strSQL & " WHERE customer.dealer=" _
    & chr(34) & me!incident_number  & chr(34) _
    & ";" Set rst = dbs.OpenRecordset(strSQL)

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Quote:

>     Set dbs = CurrentDb()
>     strSQL = "SELECT * FROM customer"
>     strSQL = strSQL & " WHERE customer.dealer='"& me!incident_number
>     &  "';" Set rst = dbs.OpenRecordset(strSQL)

>     My problem is that the variable 'incident_number' is system
> generated from another application and within the string type
> variable are ASCII text characters that are causing my query to crash.

>     The character is a ' (single quote).  Now within the code above
> the query has to have a single quote to distinquish a text variable
> from the remaining query.

>     How can I get around is this issue?

> Scott



Mon, 05 Dec 2005 23:35:14 GMT  
 I'm having a problem with a 'special' character in a query
strSQL = strSQL & " WHERE customer.dealer='" & Replace(me!incident_number,
"'", "''") &  "';"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Quote:
>     Set dbs = CurrentDb()
>     strSQL = "SELECT * FROM customer"
>     strSQL = strSQL & " WHERE customer.dealer='"& me!incident_number &
"';"
>     Set rst = dbs.OpenRecordset(strSQL)

>     My problem is that the variable 'incident_number' is system generated
> from another application and within the string type variable are ASCII
text
> characters that are causing my query to crash.

>     The character is a ' (single quote).  Now within the code above the
> query has to have a single quote to distinquish a text variable from the
> remaining query.

>     How can I get around is this issue?

> Scott



Mon, 05 Dec 2005 23:46:19 GMT  
 I'm having a problem with a 'special' character in a query
Someone had suggested using ']'

"SELECT '[ quote $val ]' AS ret"

Has anyone tried this?

Scott


Quote:
> strSQL = strSQL & " WHERE customer.dealer='" & Replace(me!incident_number,
> "'", "''") &  "';"

> --
> John Viescas, author
> "Microsoft Office Access 2003 Inside Out" (coming soon)
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> (Microsoft Access MVP since 1993)


> >     Set dbs = CurrentDb()
> >     strSQL = "SELECT * FROM customer"
> >     strSQL = strSQL & " WHERE customer.dealer='"& me!incident_number &
> "';"
> >     Set rst = dbs.OpenRecordset(strSQL)

> >     My problem is that the variable 'incident_number' is system
generated
> > from another application and within the string type variable are ASCII
> text
> > characters that are causing my query to crash.

> >     The character is a ' (single quote).  Now within the code above the
> > query has to have a single quote to distinquish a text variable from the
> > remaining query.

> >     How can I get around is this issue?

> > Scott



Tue, 06 Dec 2005 00:37:18 GMT  
 I'm having a problem with a 'special' character in a query
You do need to delimit names that contain blanks or special characters with
brackets, but that's not your original problem.  You're trying to build a
predicate with a string literal, and the variable you're embedding in the
literal contains quotes.  My or Sandra Daigle's solution should do the
trick.  You basically want to change:

WHERE Customer.Dealer = 'John's used cars'

To (my solution):

WHERE Customer.Dealer = 'John''s used cars'

Or (Sandra's solution):

WHERE Customer.Dealer = "John's used cars"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Quote:
> Someone had suggested using ']'

> "SELECT '[ quote $val ]' AS ret"

> Has anyone tried this?

> Scott



> > strSQL = strSQL & " WHERE customer.dealer='" &

Replace(me!incident_number,
Quote:
> > "'", "''") &  "';"

> > --
> > John Viescas, author
> > "Microsoft Office Access 2003 Inside Out" (coming soon)
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> > (Microsoft Access MVP since 1993)


> > >     Set dbs = CurrentDb()
> > >     strSQL = "SELECT * FROM customer"
> > >     strSQL = strSQL & " WHERE customer.dealer='"& me!incident_number &
> > "';"
> > >     Set rst = dbs.OpenRecordset(strSQL)

> > >     My problem is that the variable 'incident_number' is system
> generated
> > > from another application and within the string type variable are ASCII
> > text
> > > characters that are causing my query to crash.

> > >     The character is a ' (single quote).  Now within the code above
the
> > > query has to have a single quote to distinquish a text variable from
the
> > > remaining query.

> > >     How can I get around is this issue?

> > > Scott



Tue, 06 Dec 2005 03:56:04 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. character '^' inside a sql query

2. I'm having problems querying access database

3. SQL and ' (ASCII 39) character problem

4. Special Character (TM) in form's caption.

5. Can't store special characters in files properly

6. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

7. Problem Querying with 'Like' Statement

8. Special 'always on top' windows

9. Typing '*' character in Richtext control

10. Escape '%' character

11. '|' Character in SQL Syntax

12. '|' Character in SQL Syntax

 

 
Powered by phpBB® Forum Software