Syntax error in SQL

  • Thread starter Thread starter Dutchy
  • Start date Start date
D

Dutchy

Following SQL statement returns a syntax error
SQL_mut = "SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER,
MUTUAL.NAAM FROM MUTUAL WHERE MUTUAL.NAAM = '" & TB_pat!BOND & "'; "

The output is

SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER, MUTUAL.NAAM FROM
MUTUAL WHERE MUTUAL.NAAM = 'FED.DER BEROEPSMUTUALITEITEN A'PEN';

The syntax error occurs because TB_pat!Bond for a record is 'FED.DER
BEROEPSMUTUALITEITEN A'PEN
the ' in A'PEN cause the problem

How to avoid the error??

Thanks
 
Following SQL statement returns a syntax error
SQL_mut = "SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER,
MUTUAL.NAAM FROM MUTUAL WHERE MUTUAL.NAAM = '" & TB_pat!BOND & "'; "

The output is

SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER, MUTUAL.NAAM FROM
MUTUAL WHERE MUTUAL.NAAM = 'FED.DER BEROEPSMUTUALITEITEN A'PEN';

The syntax error occurs because TB_pat!Bond for a record is 'FED.DER
BEROEPSMUTUALITEITEN A'PEN
the ' in A'PEN cause the problem

How to avoid the error??

Thanks
Click to expand...
use:
WHERE MUTUAL.NAAM = " & chr(34) & TB_pat!BOND & chr(34) & "; "

becomes
WHERE MUTUAL.NAAM = "FED.DER BEROEPSMUTUALITEITEN A'PEN";
 
Dutchy said:
Following SQL statement returns a syntax error
SQL_mut = "SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER,
MUTUAL.NAAM FROM MUTUAL WHERE MUTUAL.NAAM = '" & TB_pat!BOND & "'; "

The output is

SELECT MUTUAL.STRAAT, MUTUAL.PLAATS, MUTUAL.POSTNUMMER, MUTUAL.NAAM FROM
MUTUAL WHERE MUTUAL.NAAM = 'FED.DER BEROEPSMUTUALITEITEN A'PEN';

The syntax error occurs because TB_pat!Bond for a record is 'FED.DER
BEROEPSMUTUALITEITEN A'PEN
the ' in A'PEN cause the problem
Click to expand...


Either double up the single quote in the Bond value:

. . . '" & Replace(TB_pat!BON, "'", "''") & "'; "

or use double quotes around the value:

. . . """ & TB_pat!BON & """; "
 
Both solutions work... thnaks
JP
Marshall Barton said:
Either double up the single quote in the Bond value:

. . . '" & Replace(TB_pat!BON, "'", "''") & "'; "

or use double quotes around the value:

. . . """ & TB_pat!BON & """; "
Click to expand...
 
Back
Top