Insert into syntax error - Its probably something easy

[CodyMonster]

I'm getting a syntax error for the insert into part of this code..
I'm probably doing something easy and just over looking a " or something.
Totally frustrated.

Dim strSQL As String

BWreportdate = XlApp.Range("A13").Value   '<-- this is a date in Excel

strSQL = "insert into 9-Daily_Physical_Date([Date])" & "Values(#BWReportDate#);"
DoCmd.RunSQL strSQL

 

[Micron]

re post #9
Is 2DateAquire a query, field or form control name whose name you did not change also? Or is your recordset based on the table 2DateAquire? Somewhere, you have overlooked something that still has the old name.
#8
dim dteAcquire as string
dteAcquire = forms!frmMyForm.txtDate' obviously, you use your own correct object references
strSql = "INSERT INTO Insert into 9-Daily_Physical_Date([Date]) values(#" & dteAcquire & "#);" NOTE: I don't remember if you need the two # if dteAcquire is dim'd as a date. You will if you need to dim it as a string. Best part of assigning values to variables is that you can mouse over them during code step through to see if they're being 'pulled in', or it is easy to query them in the immediate window. The method is to always ask as a question: ?dteAcquire then press Enter. You can do this for many things, such as a recordset count: ?rs.recordcount
Very good that you looked up reserved word list!

 

[CodyMonster]

re post #9
Is 2DateAquire a query, field or form control name whose name you did not change also? Or is your recordset based on the table 2DateAquire?

Its a table called 2DateAquire. I've tried to add different tables.. getting the same error for all of them. So, for some reason, I'm not able to acquire data from any of the tables in the DB.

I'm starting to think that this is something in the tools-->reference and maybe I don't have the correct reference ticked off?

#8
dim dteAcquire as string
dteAcquire = forms!frmMyForm.txtDate' obviously, you use your own correct object references
strSql = "INSERT INTO Insert into 9-Daily_Physical_Date([Date]) values(#" & dteAcquire & "#);" NOTE: I don't remember if you need the two # if dteAcquire is dim'd as a date. You will if you need to dim it as a string. Best part of assigning values to variables is that you can mouse over them during code step through to see if they're being 'pulled in', or it is easy to query them in the immediate window. The method is to always ask as a question: ?dteAcquire then press Enter. You can do this for many things, such as a recordset count: ?rs.recordcount
Very good that you looked up reserved word list!

Tried:

dim dteAcquire as string
dteAcquire = [2dateAquire]![Date_0] <---get error 424 object required.

So, I'm thinking the 424 error is due to not being able to pull from the table?
Since I'm getting the error, I'm not able to question in the Immediate window.

Thanks for the pointers Micron.. Although this isn't solved yet, its helping..

 

[Micron]

Its a table called 2DateAquire. Now that I understand what this is, I can tell you that you cannot retrieve data from a table in this manner: dteAcquire = [2dateAquire]![Date_0].
Access thinks you are trying to reference an object because of the ! and the way you're using it. It would probably be OK if this was a Form!ControlName reference you were working with. To assign a value to a variable, you need to first retrieve it with a domain lookup function OR sql and recordset, OR off of a form control where the form is bound to a table or query. Maybe research DLOOKUP and see what you think, but you'd need criteria for this in order to retrieve a value other than just the first in a domain (table or query result).

I am quite sure that regardless of the foregoing, references have nothing to do with your immediate problem. Post back with your thoughts on how you want to go about it.