insert sql query please help this should be easy

[Chris]

You need to include the single qoute in your SQL statement:
This because you declara as String.

 
SQL = "INSERT INTO CandidateHistory(CandidateHistoryID)VALUES ('" & CandidateIdNumber & "')"

Originally posted by johnnyboy23
this is my statement

SQL = "INSERT INTO CandidateHistory(CandidateHistoryID)VALUES (" & CandidateIdNumber & ")"

but the value that i want should come from my varible because that value changes all the time
called

dim CandidateIdNumber as string

this this correct????

[paulw]

Note that the difference between

INSERT INTO Table1 (FieldName) SELECT Table2.FieldName2 FROM Table2;

and

INSERT INTO Table1 (FieldName) VALUES (SomeValue);

is that the former is a multi record append query and the latter is for one record only.

You may be having problems with data types since you appear to be adding a numeric value but you have declared the variable as a string. Chris is right when he says that you need to enclose the string in quotes, but if it is meant to be a numeric value you should explicitly do a type cast (i.e. CInt(CandidateIdNumber))

Cheers,

P.