error 3075...making me nuts

[graccess]

First time posting here and a newbie at Access so please forgive me if I miss a pertinent piece of info, will be more than happy to revise and thanks in advance for helping. Building a form that will allow me to add several fields of user input simultaneously from a combo box, text, check box, and a date into my subform.

Upon clicking the button I receive the following error: Run-time error '3075' Syntax error (missing operator) in query expression 'test','test corp','test sector','test subsector','#2014-01-11#','-1')'.

Private Sub cmdAdd_Click()


'add data to table
CurrentDb.Execute "INSERT INTO [company table] ([ticker], [company], [sector], [subindustry], [lastupdate], [flag]) " & _
" VALUES(" & Me.cboTicker & "','" & Me.txtCompany & "','" & Me.txtSector & "','" & Me.txtSubIndustry & "','" & _
Format(dtLastupdate, "\#yyyy-mm-dd\#") & "','" & Me.cbxFlag & "')"

Not sure what VBA is finding invalid....

[pbaldy]

You don't want the apostrophes around the date value, just the #. It also appears you have an apostrophe after the first value but not before. They need to match up. Numeric data types wouldn't have any delimiter. This may help:

BaldyWeb-Immediate window

[JoeM]

Note only text fields should be enclosed in quotes. It looks like your date has both quotes and pound signs. I don't think that is correct.
Also, if your cbxFlag field is a numeric field and not text, it should not have quotes around it either.

A little trick that I often do when trying to build and debug SQL code is to write it out to Message Box so I can visually look at what is being built first before executing it to make sure it looks correct. Once you have it working, you can get rid of the Message Box in your code.

[graccess]

Thanks Paul and Joe. When I remove the quotes around the date field I receive a compile error: expected: expression.

this is how i rewrote the datefield expression after removing the quotes:
\#yyyy-mm-dd\#

Paul, you noted I have an apostrophe after the first value and not before, can you be more specific, I don't see that although my eyes are getting a bit blurry.

thx

[graccess]

Disregard, found it and I believe its fixed now but of course a new error was generated, 3134 INSERT INTO error, which I know probably warrants a separate thread but if you happen to see that error in that syntax written earlier, please let me know.
thx again. both your replies resolved the 3075 error.

[pbaldy]

Have you tried the technique in post 2 to see the finished SQL? What's the text of the error message?

[June7]

Is dtLastupdate a date or text type field?

[graccess]

Run-time error '3134:

Syntax error in INSERT INTO statement

[pbaldy]

And what is the SQL?

[graccess]

dtLastupdate is a date field

[graccess]

SELECT [Companytable].[Company ID], [Companytable].Ticker FROM Companytable ORDER BY [Ticker];

[pbaldy]

That's a simple SELECT query, not an append query.

[graccess]

didn't get that far yet. this is the only code I have to add my input fields into the subform.

CurrentDb.Execute "INSERT INTO companytable (ticker, company, sector, subindustry, lastupdate, flag) " & _
" VALUES(" & Me.cboTicker & " ','" & Me.txtCompany & "','" & Me.txtSector & "','" & Me.txtSubIndustry & "','" & _
format (me.dtLastupdate, \#yyyy-mm-dd#\) & me.cbxFlag & ")"

now just getting a compile error.

[pbaldy]

If you use the technique I mentioned you should see the problem.

[pbaldy]

Oh, and the Format() function still needs the double quotes.