Insert Into statment - Error 3134 when inserting date field

What in the world is wrong with this simple line of code?

 dbs.Execute "INSERT INTO tempBankStatement (category, amount, date) VALUES ('Beginning Balance', " & myBegBal & ", #" & StartDate & "#);"

it keeps running "Error 3134 syntax error in INSERT INTO STATEMENT"

i've been fooling around with this for an hour!  it runs the error even if i insert a test date.

dbs.Execute "INSERT INTO tempBankStatement (category, amount, date) VALUES ('Beginning Balance', " & myBegBal & ", #7/1/2000#);"

i've changed the format of the date, used chr(35) etc.

the statement works fine if i remove the date information.

Who is Participating?
raizonConnect With a Mentor Commented:
If your DB is an Access DB then using the # is correct.  However I agree with bobbit that you need to take a look at your column 'date'.  If you want to keep it date then in your sql statment you need to wrap it with brackets.

dbs.Execute "INSERT INTO tempBankStatement (category, amount, [date]) VALUES ('Beginning Balance', " &
myBegBal & ", #7/1/2000#);"

Try replacing the # with a single quote '. ('7/1/2000')
have you tried renaming your "date" column to something other than the keyword date?

INSERT INTO tempBankStatement (category, amount, myDate <--)

laurapAuthor Commented:
thanks for everyone's help..........
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.