Help with SQL syntax error

[bopsgtir]

Hi all im trying to insert a record with the below code in vba but i cant seem to get the code right?? any suggestions


sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (Date(),"Card Ordered For New Starter " & [FirstName] & " " & [Surname]"

[Toyman]

Hi all im trying to insert a record with the below code in vba but i cant seem to get the code right?? any suggestions


sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (Date(),"Card Ordered For New Starter " & [FirstName] & " " & [Surname]"

Dim sName as String

sName = Me.FirstName & " " & Me.Surname

sSql = "INSERT INTO tbl_test ( DateOrdered, OrderReason)
SELECT Date() AS sDate, 'Card Ordered For New Starter '" & sName & " AS stext;"

[jzwp11]

I don't know if it was just a typo when you copied the query to your post but you are missing the closing parenthesis. Additionally, you have an extra double quote. Also what are [FirstName] and [Surname]? I assume they are form controls on the form from which you are executing this code, if so you should reference them with me. me.[FirstName]. Also, Date() is technically a variable value and since it is a date, it should be set off with # signs

sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (#" & Date() & "#,Card Ordered For New Starter " & me.[FirstName] & " " & me.[Surname] & ")"

I think you also have to set off the two controls with single quotes. I would be tempted to construct the string and store it in a variable and then reference the variable in sSQL

dim myphrase as string

myphrase= "Card Ordered For New Starter " & me.[FirstName] & " " & me.[Surname]

Then the sSQL would be as follows

sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (#" & Date() & "#,'" & myphrase & "')"

[June7]

Even literal strings need apostrophe delimiter in SQL. Since you are building a string for the second field, one set of apostrophes will work:

sSQL = "INSERT INTO TblFuelCard (DateOrdered, OrderReason) VALUES (#" & Date() & "#, 'Card Ordered For New Starter " & Me.[FirstName] & " " & Me.[Surname] & "')"

[bopsgtir]

Thank you all, The Final code i used was

sSQL = "INSERT INTO TblFuelCard (DateOrdered, OrderReason) VALUES (#" & Date() & "#, 'Card Ordered For New Starter " & Me.[FirstName] & " " & Me.[Surname] & "')"

supplied by June7 many thanks, this is a real learning curve.