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

 

[CodyMonster]

Now trying it this way..
I have the date I need in another table..[2-date_Aquire]![Date-0]
Getting Run-Time Error '3134': Syntax Error in Insert Into statement

any help would be appreciated.

If DCount("*", "9-Daily_Physical_Date") = 0 Then
Dim strSQL As String
strSQL = "Insert into 9-Daily_Physical_Date([Date]) values([2-date_Aquire]![Date-0]);"
Debug.Print strSQL
CurrentDb.Execute strSQL

 

[Micron]

"insert into 9-Daily_Physical_Date([Date])" & "Values(#BWReportDate#);"
"INSERT INTO 9-Daily_Physical_Date([Date]) VALUES (#" & BWReportDate "#);"

"Insert into 9-Daily_Physical_Date([Date]) values([2-date_Aquire]![Date-0]);"
"INSERT INTO 9-Daily_Physical_Date([Date]) VALUES (#" & [2-date_Aquire]![Date-0] & "#);"

Methinks the issue is missing # around your date value or in first case, it is in the wrong place. If neither of these work, post your debug.print result.
Observations:
- Looks like you have a field called [Date]. Date is a reserved word, so this would be bad form.
- Never use special characters (exception: _ ) in object names. I have seen situations where 9-Daily would be interpreted as "9 minus Daily".
- If [Date-0] is a field name, it's another bad form example. However, if it's supposed to be [Date]-0, I think you have it wrong.
I would opt to see if the first example works before dealing with the second, because you've made a few changes and might have introduced additional problems that my suggestion won't fix.

 

[CodyMonster]

Did what you suggested, now getting..
Run-time Error '424': Object required

The reason for the 9-Daily is mostly to track the sequence of tables being created. By putting a number in front of it, I can follow the routine.
I can just take out the "-"
Same with the [Date-0], which is just the current date. I have [Date-1] which is the previous day. I can just change these to [Date_0] and [Date_1]

Thanks for the help..
Hopefully I can get this up and running soon.

 

[Micron]

the error strongly suggests the problem is with your code, not the sql. suggest you post the code and indicate on which line(s) the error(s) generate what error numbers & message text. Debug print your sql, paste it into a new query and see if it works to determine there aren't any issues with that also.

 

[CodyMonster]

If DCount("*", "9-Daily_Physical_Date") = 0 Then
Dim strSQL As String
[I]strSQL = "Insert into 9-Daily_Physical_Date([Date]) values(#" & [2-date_Aquire]![Date-0] & "#);"[/I] <----error
Debug.Print strSQL
CurrentDb.Execute strSQL

Debug.Print output

Insert into 9-Daily_Physical_Date([Date]) values(#[2-date_Aquire]![Date-0]#);

Maybe its the naming of the fields that is a problem..?

 

[Micron]

[Debug.Print output
Insert into 9-Daily_Physical_Date([Date]) values(#[2-date_Aquire]![Date-0]#);
Maybe its the naming of the fields that is a problem..?

Your parameter is not being resolved. This should be a date - like 01/01/2015

 

[CodyMonster]

Yeah, I see what you mean...
Any suggestions? Thanks for the help.. I really appreciate it.

 

[CodyMonster]

So, in order to break down where the problem might be.. I started to look at each aspect of the DB.

Dim d As Database
Dim r As Recordset
Dim CurrentDate As Field
Set d = CurrentDb()
Set r = d.OpenRecordset("[2DateAquire]")
Set CurrentDate = ["Date_0"]

I changed the name of the table from 2-Date_Aquire to 2DateAquire. Just to fix any bad form.
But now I get Run-time error 3078
cannot find the input table or query [2DateAquire].
Its spelled correctly. Its there. But this is most likely why the date is not being resolved.

 

[CodyMonster]

And I found this..

Microsoft Access tips: Problem names and reserved words in Access

A list of Problem names and reserved words in Access..