Problem with "Insert Into" in SQL

[CDM]

I have a "Insert Into" statement using the "Values" keyword to add a row to
my table. I'm using variables instead of literal values. Example:

INSERT INTO mytable(Field1, Field2,..Field6) VALUES (Var1,Var2,...Var6)

I'm getting an error message that says, "Expected 6 parameters." I'm
wondering if variables don't work? and, if not, is there some other way to
insert them?

 

[dymondjack]

INSERT INTO mytable(Field1, Field2,..Field6) VALUES (Var1,Var2,...Var6)

You need to take the variables out of the string and contencate the values
into the string.

strSQL = "INSERT INTO mytable(Field1, ...) " _
& "VALUES (" & Var1 & ", " & Var2 & "," ... & Var6 & ")"

That way the SQL is taking the values of the variables, rather than
inserting the variable names.

At least, I think thats what it is... I haven't spent a lot of time using
insert queries, but the above is standard procedure for entering variable
values into an SQL string.

hth


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[dymondjack]

Also, keep in mind that if you are using string datatype variables, you may
need to enclose them in single quotes within the SQL statement. Here's a
sample vased on Var1 being a String, Var 2 being a string, and Var3 being
some sort of numeric datatype (long, byte, etc)


strSQL = "INSERT INTO mytable(Field1, ...) " _
& "VALUES ('" & Var1 & "', '" _
& Var2 & "', " _
& Var3 &")"


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Douglas J. Steele]

Just be aware that text values need to be delimited with quotes, and dates
need to be delimited with # (and must be in a format Access will recognize).

That means something like:

strSQL = "INSERT INTO mytable(NumericField, TextField, DateField, ...) " _
& "VALUES (" & Var1 & ", '" & Var2 & "'," & _
Format(Var3, "\#yyyy\-mm\-dd\#") & ... & Var6 & ")"

Just to be explicit, that second line is

& "VALUES (" & Var1 & ", ' " & Var2 & " ' ," & _

Note that it won't work if var2 contains an apostrophe.