Insert Text to a table

  • Thread starter Thread starter spartacus26
  • Start date Start date
S

spartacus26

Hi, I'm trying to insert text into a table field. The code I'm using is a
simple "INSERT INTO..." code, now my problem is when I've written my code I'm
using:

....VALUES('" & txtVal & "')

now some of the text I'm entering contain vba used coding (example: a') the
apostrophe. How can I go about writing the code to still enter the text as is
(a').
 
Hi, I'm trying to insert text into a table field. The code I'm using is a
simple "INSERT INTO..." code, now my problem is when I've written my code I'm
using:

...VALUES('" & txtVal & "')

now some of the text I'm entering contain vba used coding (example: a') the
apostrophe. How can I go about writing the code to still enter the text as is
(a').
Click to expand...

...VALUES(""" & txtVal & """).... "
For clarity, the quotes are:
...VALUES(" " " & txtVal & " " ").... "
 
fredg - this worked about 95% of the way. The next problem now facing is
there is a text that is being written over as follows (/") that is / and "

so now there is an error in syntax with "...VALUE(""" & txVal & """)..."
 
fredg - this worked about 95% of the way. The next problem now facing is
there is a text that is being written over as follows (/") that is / and "

so now there is an error in syntax with "...VALUE(""" & txVal & """)..."
Click to expand...

re: text that is being written over as follows (/") that is / and "
I have no idea of what it is you mean by the above statement.
We don't charge by the word. It's OK if you elaborate a bit. ;-)
Let's have a real life example of what it is you mean.
 
sorry about that...ok, here is the scenario:
I have a list of 2 character values (example: I0,7U,99,a',##,H&,/",Mm)
This is stored in a table called "2List"....I have created code to
automatically take each value within that list and place it into another
table "finallist". The values in "finallist" have multi records, now the code
takes those records and places the values in "2List" that are supposed to
match (example: 1111-IO, 1111-H&). **the last two characters make the
finished value**

As you can see in the "2List" examples above there is a value = /"

when the code to write to "finallist" runs, it comes back with a syntax
error because it sees the " (quotation) in that value as another "
(quotation) in: ....VALUES(""" & txtVal & """)....

txVal = the values in "2List", the code runs through each record until end
of file.

I hope I have explained my problem better...thanks for your help!!!
 
Awesome, that worked to the "T"...thanks again for your help fredg!!

fredg said:
sorry about that...ok, here is the scenario:
I have a list of 2 character values (example: I0,7U,99,a',##,H&,/",Mm)
This is stored in a table called "2List"....I have created code to
automatically take each value within that list and place it into another
table "finallist". The values in "finallist" have multi records, now the code
takes those records and places the values in "2List" that are supposed to
match (example: 1111-IO, 1111-H&). **the last two characters make the
finished value**

As you can see in the "2List" examples above there is a value = /"

when the code to write to "finallist" runs, it comes back with a syntax
error because it sees the " (quotation) in that value as another "
(quotation) in: ....VALUES(""" & txtVal & """)....

txVal = the values in "2List", the code runs through each record until end
of file.

I hope I have explained my problem better...thanks for your help!!!

:
Click to expand...
*** snipped ***

In other words, sometimes you'll have a " in the string and sometimes
a ' and other times neither?

Here is how I handle these situations. Test for the apostrophe.
This will work whether there is an ', ", or neither in the string

If InStr(strValue, "'") = 0 Then ' No apostrophe
strSQL = "Insert Into [ImportData2] (ccNumber) Values ('" & strValue &
"');"
Else
' there is an apostrophe
strSQL = "Insert Into [ImportData2] (ccNumber) Values (""" & strValue
& """);"
End If

CurrentDb.Execute strSQL, dbFailOnError

Change strValue to whatever your variable name is, and of course use
your own Insert Into Table and Fielhd names.
Click to expand...
 
Back
Top