I'm having some problems with code I've written to insert data into a table.

I've used the following with no problems:

CurrentDb.Execute "INSERT INTO tblVialDepth(Depth) VALUES (2000)"

However, if I try to use a variable like this:

Dim sngTestValue As Single
sngTestValue = "2345"
CurrentDb.Execute "INSERT INTO tblVialDepth(Depth) VALUES sngTestValue"

I get the following run-time error 3134 - Syntax error in INSERT INTO statement.

The reason I need to use a variable is that I will be changing the value of the variable based on a query (hence the query forum) and run a series of IF THEN statement to put all the values I want into the table.

Any help with how to have a variable name like sngTestValue work in the SQL statement instead of having to use a fixed value like (2000) would be greatly appreciated.

Bill

try this. it may need quotes if it isnt a numeric field

CurrentDb.Execute "INSERT INTO tblVialDepth(Depth) VALUES " & sngTestValue

Hi

You don't need the quotes around the value of your variable as you're declaring it as a numeric datatype (single).............

I tried both of the following but I still get run-time error 3134 syntax error in INSERT TO statement:

CurrentDb.Execute "INSERT INTO tblVialDepth(Depth) VALUES" & sngTestValue
CurrentDb.Execute "INSERT INTO tblVialDepth(Depth) VALUES" & "sngTestValue"

Hi

Have you tried the one I posted above ?

Thanks Alan_G

The one you posted worked perfect (g).

@Alan,

Thanks for the assist, forgot that the ( ) are needed.