Syntax problen using Insert sql statement

[cyd44]

Hi Again,

I really struggle with the syntax using SQL in VBA and need help yet again.

I simply wish to insert unbound form variables into an SQL Server table and have the following code:-

MyName = Me.Cust_Name (this is text)
MyAdd = Me.Address (this is text)
MyNum = Me.Contact_Number (this is integer)
MyMail = Me.E_Mail (this is text)
StrSQL = "INSERT INTO dbo_tblCustomer" & _
          "(Cust_Name, Address, Contact_Number, E_Mail) VALUES ('"  & MyName & '" , "' & MyAdd & '"," & MyNum & ","' & MyMail & '")"
DoCmd.RunSQL (StrSQL)

I am getting a syntax error and know it is something to do with the quotes and ampersands but am at a total loss.

Could anyone please help

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you've reversed the double and single quotes after the myname variable plus other places.

 

[mdlueck]

<snip?

 VALUES ('"  & MyName & '" , "' & MyAdd

That to me jumps out as looking odd, as I would have expected the type of quote characters needing to be paired in an inner / outer order, no in the same order on each side of MyName