Please, Need help with this Dynamic SQL Code...

[matrekz42]

Hello friends,

I'm trying to run this Dynamic SQL in VB, but I keep getting "Compile Error" Syntax Error, in the "Bolded" area. I'm only trying to change these fields for the second query listed, should I remove the first, and repeat steps? The reason why I'm doing this, is because the table name changes from month to month. What do you Guru's recommend?




Private Sub Command0_Click()
' Declare DAO
Dim qdfCurr As DAO.QueryDef
' Declare String Name
Dim strSQL As String
' Generation of SQL Code
strSQL = "SELECT dbo_" & Format(Date, "mmm") & Format(Date, "yy").id, dbo_" & Format(Date, "mmm") & Format(Date, "yy").priority, dbo_" & Format(Date, "mmm") & Format(Date, "yy").clss, dbo_" & Format(Date, "mmm") & Format(Date, "yy").ky, dbo_" & Format(Date, "mmm") & Format(Date, "yy").date, dbo_" & Format(Date, "mmm") & Format(Date, "yy").code, FROM dbo_" & Format(Date, "mmm") & Format(Date, "yy")
' Query Definitions will be sent to "MyQuery"
Set qdfCurr = CurrentDb.QueryDefs("Current_Month_Date")
Set qdfCurr = CurrentDb.QueryDefs("Get_Report60")
' The query definitions are equal to the String with the stored SQL
qdfCurr.SQL = strSQL
End Sub

[DThreadgill]

Hello friends,

I'm trying to run this Dynamic SQL in VB, but I keep getting "Compile Error" Syntax Error, in the "Bolded" area. I'm only trying to change these fields for the second query listed, should I remove the first, and repeat steps? The reason why I'm doing this, is because the table name changes from month to month. What do you Guru's recommend?




Private Sub Command0_Click()
' Declare DAO
Dim qdfCurr As DAO.QueryDef
' Declare String Name
Dim strSQL As String
' Generation of SQL Code
strSQL = "SELECT dbo_" & Format(Date, "mmm") & Format(Date, "yy").id, dbo_" & Format(Date, "mmm") & Format(Date, "yy").priority, dbo_" & Format(Date, "mmm") & Format(Date, "yy").clss, dbo_" & Format(Date, "mmm") & Format(Date, "yy").ky, dbo_" & Format(Date, "mmm") & Format(Date, "yy").date, dbo_" & Format(Date, "mmm") & Format(Date, "yy").code, FROM dbo_" & Format(Date, "mmm") & Format(Date, "yy")
' Query Definitions will be sent to "MyQuery"
Set qdfCurr = CurrentDb.QueryDefs("Current_Month_Date")
Set qdfCurr = CurrentDb.QueryDefs("Get_Report60")
' The query definitions are equal to the String with the stored SQL
qdfCurr.SQL = strSQL
End Sub

matrekz42

Try this:

Format(Date(),"mmm") & "" & Format(Date(),"yy")

[bassman]

Hello friends,

I'm trying to run this Dynamic SQL in VB, but I keep getting "Compile Error" Syntax Error, in the "Bolded" area. I'm only trying to change these fields for the second query listed, should I remove the first, and repeat steps? The reason why I'm doing this, is because the table name changes from month to month. What do you Guru's recommend?




Private Sub Command0_Click()
' Declare DAO
Dim qdfCurr As DAO.QueryDef
' Declare String Name
Dim strSQL As String
' Generation of SQL Code
strSQL = "SELECT dbo_" & Format(Date, "mmm") & Format(Date, "yy").id, dbo_" & Format(Date, "mmm") & Format(Date, "yy").priority, dbo_" & Format(Date, "mmm") & Format(Date, "yy").clss, dbo_" & Format(Date, "mmm") & Format(Date, "yy").ky, dbo_" & Format(Date, "mmm") & Format(Date, "yy").date, dbo_" & Format(Date, "mmm") & Format(Date, "yy").code, FROM dbo_" & Format(Date, "mmm") & Format(Date, "yy")
' Query Definitions will be sent to "MyQuery"
Set qdfCurr = CurrentDb.QueryDefs("Current_Month_Date")
Set qdfCurr = CurrentDb.QueryDefs("Get_Report60")
' The query definitions are equal to the String with the stored SQL
qdfCurr.SQL = strSQL
End Sub

Try knocking out double quotes for the date format values and substitute 'mmm' instead of "mmm", and similarly 'yy' instead of "yy".