Apostrophe in a text field causes error in query

A

Alp

Hi Experts,

Back again. I have a query that refuses to proceed when there is an ' in a
text field it needs to process. Any help or guidance to resolve this issue
would be greatly appreciated.

As an example when the field contains A' FAMOSA as its data the query
produces the error:
Syntax error (missing operator) in query expression "A' FAMOSA', ")'

Thanks in advance.

Alp

The code behind the form is:

Private Sub PrintBtn_Click()
On Error GoTo Err_PrintBtn_Click
'Make the entries to nametags table
Dim strSQL As String
'strSQL = "INSERT INTO tb_tags ( tag_co_id, tag_type ) " & _
' "VALUES (" & Forms!fr_nt_unnamed!coid & ", '" &
Forms!fr_nt_unnamed!attTipAd & "' );"
strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
 
G

Guest

You need to do an instring then reconstruct it.

Dim x as integer, st1 as string, st2 as string, st3 as string
x = inst(text,"'")
st1 = left(text,x-1)
st2 = right(textx+1)
st3 = st1 & " " & st2

or maybe st1 & chr(?) & st3

I'm not sure how you would toss that into a QRY if you really need it.

st3 = st1 & chr(34) & ch(?) & chr(34) & st2

with ? being whatever chr a ' is. I forget.


(e-mail address removed)
 
D

Douglas J. Steele

You need to replace the single apostrophe in the text with two apostrophes
in a row. Assuming you're using Access 2000 or newer, try:

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!attTipAd, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!BoothNo, "'", "''") & "' );"

where that's Replace(..., " ' ", " ' ' ")
 
K

Ken Snell [MVP]

You need to double up the ' character in the text string so that ACCESS sees
it as a single, embedded ' character.

I haven't rewritten the entire SQL, but here is an example of how you can
use the Replace function to do this for you (see the first "Value" item in
the following expression):

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Replace(Forms!DataEntry!fr_nt_unnamed_emb!coid, "'",
"''", 1, -1, vbTextCompare) & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"
 
A

Alp

Hi Doug,

A life saver as usual. The line that gives me the headache is the one that
has CoNmSel which takes the company name from the form. Some companies like
to have such characters (I do not know why) in their names. When I altered
that line with what you have suggested I started getting "Invalid procedure
call or argument" error. What next?

Or did I had to replace the whole strSQL?

Thanks again for a lightning fast response.

Alp

Douglas J. Steele said:
You need to replace the single apostrophe in the text with two apostrophes
in a row. Assuming you're using Access 2000 or newer, try:

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!attTipAd, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!BoothNo, "'", "''") & "' );"

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Alp said:
Hi Experts,

Back again. I have a query that refuses to proceed when there is an ' in a
text field it needs to process. Any help or guidance to resolve this issue
would be greatly appreciated.

As an example when the field contains A' FAMOSA as its data the query
produces the error:
Syntax error (missing operator) in query expression "A' FAMOSA', ")'

Thanks in advance.

Alp

The code behind the form is:

Private Sub PrintBtn_Click()
On Error GoTo Err_PrintBtn_Click
'Make the entries to nametags table
Dim strSQL As String
'strSQL = "INSERT INTO tb_tags ( tag_co_id, tag_type ) " & _
' "VALUES (" & Forms!fr_nt_unnamed!coid & ", '" &
Forms!fr_nt_unnamed!attTipAd & "' );"
strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
Click to expand...
Click to expand...
 
A

Alp

Hi Ken,

Thank you for your help. After trying the above, I have added your
additional code to the line (,1,-1,vbTextCompare) which seems to heal the
wound!

It did not work before I added that part.

Thanks a million and also special thanks for the immediate reply.

Alp


Ken Snell said:
You need to double up the ' character in the text string so that ACCESS sees
it as a single, embedded ' character.

I haven't rewritten the entire SQL, but here is an example of how you can
use the Replace function to do this for you (see the first "Value" item in
the following expression):

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Replace(Forms!DataEntry!fr_nt_unnamed_emb!coid, "'",
"''", 1, -1, vbTextCompare) & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"
--

Ken Snell
<MS ACCESS MVP>

Alp said:
Hi Experts,

Back again. I have a query that refuses to proceed when there is an ' in a
text field it needs to process. Any help or guidance to resolve this issue
would be greatly appreciated.

As an example when the field contains A' FAMOSA as its data the query
produces the error:
Syntax error (missing operator) in query expression "A' FAMOSA', ")'

Thanks in advance.

Alp

The code behind the form is:

Private Sub PrintBtn_Click()
On Error GoTo Err_PrintBtn_Click
'Make the entries to nametags table
Dim strSQL As String
'strSQL = "INSERT INTO tb_tags ( tag_co_id, tag_type ) " & _
' "VALUES (" & Forms!fr_nt_unnamed!coid & ", '" &
Forms!fr_nt_unnamed!attTipAd & "' );"
strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
Click to expand...
Click to expand...
 
A

Alp

Hi Doug,

As I explained in my response to Ken, the addition of (
....,1,-1,vbTextCompare) did the trick.

Thanks to both of you guys. With your immediate help, I can take some
sleep...(01:42 here)

Alp

Douglas J. Steele said:
You need to replace the single apostrophe in the text with two apostrophes
in a row. Assuming you're using Access 2000 or newer, try:

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!attTipAd, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel, "'", "''") & "', '"
& _
Replace(Forms!DataEntry!fr_nt_unnamed_emb!BoothNo, "'", "''") & "' );"

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Alp said:
Hi Experts,

Back again. I have a query that refuses to proceed when there is an ' in a
text field it needs to process. Any help or guidance to resolve this issue
would be greatly appreciated.

As an example when the field contains A' FAMOSA as its data the query
produces the error:
Syntax error (missing operator) in query expression "A' FAMOSA', ")'

Thanks in advance.

Alp

The code behind the form is:

Private Sub PrintBtn_Click()
On Error GoTo Err_PrintBtn_Click
'Make the entries to nametags table
Dim strSQL As String
'strSQL = "INSERT INTO tb_tags ( tag_co_id, tag_type ) " & _
' "VALUES (" & Forms!fr_nt_unnamed!coid & ", '" &
Forms!fr_nt_unnamed!attTipAd & "' );"
strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
Click to expand...
Click to expand...
 
A

Alp

Thanks Spammastergrand for your help. I have resolved the issue via the
other suggestions.

Alp
 
K

Ken Snell [MVP]

There is a bug in ACCESS with the Replace function where, if you leave those
last three arguments out (and accept the defaults), ACCESS thinks that the
vbTextCompare is a language setting (Hungarian, if I recall?). Thus, it is
unable to make a match.

Although not all agree with me, I always include those three arguments
whenever I use Replace. I've never had this bug bite me again since
implementing this practice.

--

Ken Snell
<MS ACCESS MVP>

Alp said:
Hi Ken,

Thank you for your help. After trying the above, I have added your
additional code to the line (,1,-1,vbTextCompare) which seems to heal the
wound!

It did not work before I added that part.

Thanks a million and also special thanks for the immediate reply.

Alp


Ken Snell said:
You need to double up the ' character in the text string so that ACCESS sees
it as a single, embedded ' character.

I haven't rewritten the entire SQL, but here is an example of how you can
use the Replace function to do this for you (see the first "Value" item in
the following expression):

strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Replace(Forms!DataEntry!fr_nt_unnamed_emb!coid, "'",
"''", 1, -1, vbTextCompare) & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"
Click to expand...
in
a
text field it needs to process. Any help or guidance to resolve this issue
would be greatly appreciated.

As an example when the field contains A' FAMOSA as its data the query
produces the error:
Syntax error (missing operator) in query expression "A' FAMOSA', ")'

Thanks in advance.

Alp

The code behind the form is:

Private Sub PrintBtn_Click()
On Error GoTo Err_PrintBtn_Click
'Make the entries to nametags table
Dim strSQL As String
'strSQL = "INSERT INTO tb_tags ( tag_co_id, tag_type ) " & _
' "VALUES (" & Forms!fr_nt_unnamed!coid & ", '" &
Forms!fr_nt_unnamed!attTipAd & "' );"
strSQL = "INSERT INTO Attendees ( CompanyID, AttendeeType, CompanyName,
Booth ) " & _
"VALUES (" & Forms!DataEntry!fr_nt_unnamed_emb!coid & ", '" & _
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Alp

I'll keep that in mind. Thanks again.

Alp

Ken Snell said:
There is a bug in ACCESS with the Replace function where, if you leave those
last three arguments out (and accept the defaults), ACCESS thinks that the
vbTextCompare is a language setting (Hungarian, if I recall?). Thus, it is
unable to make a match.

Although not all agree with me, I always include those three arguments
whenever I use Replace. I've never had this bug bite me again since
implementing this practice.

--

Ken Snell
<MS ACCESS MVP>

Alp said:
Hi Ken,

Thank you for your help. After trying the above, I have added your
additional code to the line (,1,-1,vbTextCompare) which seems to heal the
wound!

It did not work before I added that part.

Thanks a million and also special thanks for the immediate reply.

Alp


ACCESS
sees
Click to expand...
item
in
Click to expand...
'
in
Click to expand...
&
_
Forms!DataEntry!fr_nt_unnamed_emb!attTipAd & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!CoNmSel & "', '" & _
Forms!DataEntry!fr_nt_unnamed_emb!BoothNo & "' );"

For x = 1 To Me.ntadet
DoCmd.RunSQL strSQL
Next x

Dim stDocName As String, stWhere As String
stDocName = "Nametag Z"
stWhere = "[CompanyID] = " & Me.coid '& " AND [NameTagIssued] = No"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_PrintBtn_Click:
Exit Sub

Err_PrintBtn_Click:
MsgBox Err.Description
Resume Exit_PrintBtn_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Top