DLookup, continued

[Guest]

OK, I must be the densest one here, I'll admit it.

I was having problems with looking up a value from a table when there is a
single quote in the string.

Douglas Steele suggested the following:
strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , '" & PubName & "', " & _
" '" & Replace(loc, "'", "''") & "' );"

Well, I thought it was working, but for some reason, it isn't. The above
strSQL prints out:
INSERT INTO requests ( [PubID], [Out], [EmployeeID], [NameOfPublication],
[Location] ) values (49 , '-1', 29381 , 'Supervisor's Portable Answer Book',
'address' );

but gives an error message:
(quote)
Syntax error (missing operator) in query expression "Supervisor's Portable
Answer Book', 'address' );'.
(end quote)

EmpID is a number, NameOfPublication is text

I can't find the error. Help.

tia,

 

[Douglas J. Steele]

The problem is the quote in Supervisor's Portable Answer Book. Since you're
using a single quote as the delimiter, you need to double up any occurrences
of single quote in the string (or else use a different delimiter).

Assuming you're using Access 2000 or newer, try

strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , '" & Replace(PubName, "'",
"''") & "', " & _
" '" & Replace(loc, "'", "''") & "' );"

where that's Replace(PubName, " ' ", " ' ' ")

Alternative, you could try:

strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , " & Chr$(34) & PubName &
Chr$(34) & ", " & _
" '" & Replace(loc, "'", "''") & "' );"

Note that there's no impact on strings that don't have an embedded quote, so
it's "safe" for all values.


What's the data type of Out, by the way? If it's boolean, there shouldn't be
quotes around the -1.

 

[Guest]

Thanks again for the help. This is now working like it should.

JMorrell

The problem is the quote in Supervisor's Portable Answer Book. Since you're
using a single quote as the delimiter, you need to double up any occurrences
of single quote in the string (or else use a different delimiter).

Assuming you're using Access 2000 or newer, try

strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , '" & Replace(PubName, "'",
"''") & "', " & _
" '" & Replace(loc, "'", "''") & "' );"

where that's Replace(PubName, " ' ", " ' ' ")

Alternative, you could try:

strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , " & Chr$(34) & PubName &
Chr$(34) & ", " & _
" '" & Replace(loc, "'", "''") & "' );"

Note that there's no impact on strings that don't have an embedded quote, so
it's "safe" for all values.


What's the data type of Out, by the way? If it's boolean, there shouldn't be
quotes around the -1.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

OK, I must be the densest one here, I'll admit it.

I was having problems with looking up a value from a table when there is a
single quote in the string.

Douglas Steele suggested the following:
strSQL = "INSERT INTO requests ( [PubID], [Out], [EmployeeID],
[NameOfPublication], [Location] ) " & _
"values (" & PubID & " , '-1', " & empid & " , '" & PubName & "', " & _
" '" & Replace(loc, "'", "''") & "' );"

Well, I thought it was working, but for some reason, it isn't. The above
strSQL prints out:
INSERT INTO requests ( [PubID], [Out], [EmployeeID], [NameOfPublication],
[Location] ) values (49 , '-1', 29381 , 'Supervisor's Portable Answer Book',
'address' );

but gives an error message:
(quote)
Syntax error (missing operator) in query expression "Supervisor's Portable
Answer Book', 'address' );'.
(end quote)

EmpID is a number, NameOfPublication is text

I can't find the error. Help.

tia,