I am trying to call a variable into my Insert Statement

name = mem_first_name
strSQL = "insert into Test ( FirstName ) Values (" & name & ")"


Test is a table in an Access database. If I type the actual value then it works, but doesn't work with a variable

Since "name" is a String, then it will need to be encased in Single Quotes ' '.

Try:
name = "'" & mem_first_name & "'"
strSQL = "insert into Test ( FirstName ) Values (" & name & ")"

Hope this helps,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Rob, it doesn't work. It gives me a syntax error.

Tara,

Please try and be more descriptive with your problems.
What like is causing the error? Is it within the code, or when you try and execute the query?

Oh, and apologies, I should have said in my first post, welcome to the forum!

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Thanks!!!

I am coding in Extra (mainframe) application. I am doing a screen scrape and then appending the data into an Access table. Some of the VB commands work and some aren't. Below is the code where I am screen scraping and then trying to append this into Access:

mem_first_name = Sess0.Screen.GetString(15, 09, 12)

Const DBNAME = "C:\Work.mdb"
   Dim connection_string As String, cn As Object, cmd As Object connection_string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBNAME & ";Persist Security Info=False"

   Set cn = CreateObject("ADODB.Connection")
   Set cmd = CreateObject("ADODB.Command")

   cn.ConnectionString = connection_string
   cn.Open
   cmd.ActiveConnection = cn

   name = "'" & mem_first_name & "'"
   strSQL = "insert into Test ( FirstName ) Values (" & name & ")"
   cmd.CommandText = strSQL
   cmd.Execute

   Set cmd = Nothing
   Set cn = Nothing

End Sub
*------------------------------------*
It gives me a syntax error at the name statement.

Looks like your running VBScript yes.. ?

I dont see a declaration for the name variable?
I don't know if not declaring a variable throws a syntax error
(been a long time since I did VBScript!)

Hope this helps,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Thank you for help!!!

But this is what worked:


StrSQL = "insert into Test(FirstName) Values ('" &Cstr(mem_first_name)& "')"

It is Cstr which made it work.

Thanks!!!

Tara

Tara,

As I said, didn't know what "mem_first_name" was, or where you got it.
VBScript is a dynamic language, and while it brings benefits, its type-less
structure can make odd errors occur. Explicitly calling CStr forces the
scripting engine to treat it like a String.

Glad you got it working, please shout if you need anything else.

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>