How can i pass a date variable to this Append SQL

[Craig McKellar via AccessMonster.com]

I can not seem to pass a date variable to this Append SQL what am I missing?

Function AppendYeartoDate()
On Error GoTo Err_Append
Dim QuerySQL4 As String
Dim EndofMonth As Date

Dim Message, Title, Default, MyValue
Message = "Enter Date of Scrap Report example: 5/1/2005 "
Title = "MsgBox Date Needed" ' Define title.
Default = Date ' Set default.
MyValue = InputBox(Message, Title, Default)
Months1 = MonthName(Month(MyValue), True)
Months = Month(MyValue)
Years = Year(MyValue)
Sheet = Months1 + Trim(Str(Years))
EndofMonth = DateSerial(Years, Months + 1, 0)

QuerySQL4 = " INSERT INTO " & Years & "YeartoDate ( PartNo, Sold, Scrap
,[Date]) " & _
"SELECT " & Sheet & "Percents.PartNo, " & Sheet & "Percents.[Total Of
Sold], " & Sheet & "Percents.[Total Of Total], EndofMonth as MonthEnd " &
_
"FROM " & Sheet & "Percents;"

DoCmd.RunSQL QuerySQL4

Exit Function
Err_Append:
Response = MsgBox("No such File", vbOKOnly, "Error Message")
End Function

 

[Douglas J. Steele]

Try

QuerySQL4 = " INSERT INTO " & Years & "YeartoDate ( PartNo, Sold,
Scrap,[Date]) " & _
"SELECT PartNo, [Total Of Sold], Percents.[Total Of Total], " & _
Format(EndofMonth, "\#mm\/dd\/yyyy\#") " as MonthEnd " & _
"FROM " & Sheet & "Percents;"

You need to be passing the value of EndofMonth (properly formatted for use
in SQL) rather than the name of the variable. As well, since you're only
querying 1 table, there's no need to repeat the table name for each field.

Having said that, the idea of creating separate tables by year (or whatever)
is seldom (if ever) a good idea.