Punctuation causing errors

[Scott Whetsell, A.S. - WVSP]

I have several forms where the controls are not bound directly to a table. I
am encountering unexpected issued when it comes to using the SQL Insert code
to save the data to the appropriate tables and fields. Certain punctuation
such as semicolons and apostrophies are causing errors. From what I can
trace back, when the code runs it is reading the punctuation as part of the
code and not part of the data. How do I work around this?

Thanks for any help.

 

[Jeanette Cunningham]

Post an example or several examples of the part where the punctuation is
incorrect and we can explain how the punctuation needs to be changed to make
the query work. The punctuation is different if you are passing literal
values from when you are passing data from controls on a form. The sometimes
used "'" both sides of a string value is often best replaced by 3 double
quotes instead and sometimes you need 2 double quotes and sometimes you need
2 single quotes - it all depends on what the query is trying to do.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Here is a line of code which most prominently causes the errors.

strSQL = "INSERT INTO tbl_Narratives (NAR_DateTime, NAR_User, NAR_CCNo, " & _
"NAR_Narrative) VALUES (#" & Now() & "#, '" & UCase(CurrentUser()) & _
"', '" & Me.CFS_CCNo & "', '" & Trim(UCase(Me.NarrativeFreeLine)) & "')"

The error occurrs with the NarrativeFreeLine. I'll clarify to make sure we
are on the same page. If the value in NarrativeFreeLine is simple text it
works fine. If we put a contraction in the line, it reads the apostrophie in
that contraction as part of the code, which causes an error at runtime
because it thinks the code is ending before it actually is and doesn't carry
forward anything past the contraction's apostrophie.

As far as table setup, the field the NarrativeFreeLine writes to is a memo
field.

Thanks.

 

[Jeanette Cunningham]

It's most likely that the quote marks within the text in NarrativeFreeLine
are causing problems.

Replace
'" & Trim(UCase(Me.NarrativeFreeLine)) & "'

With
""" & Trim(UCase(Me.NarrativeFreeLine)) & """

In other words, instead of a single quote followed by a double quote, use 3
double quotes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Perfect. Thanks for your help.

It's most likely that the quote marks within the text in NarrativeFreeLine
are causing problems.

Replace
'" & Trim(UCase(Me.NarrativeFreeLine)) & "'

With
""" & Trim(UCase(Me.NarrativeFreeLine)) & """

In other words, instead of a single quote followed by a double quote, use 3
double quotes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Scott Whetsell, A.S. - WVSP"