By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,474 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,474 IT Pros & Developers. It's quick & easy.

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

matrekz42
P: 37
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
Sep 26 '07 #1
Share this Question
Share on Google+
2 Replies


P: 57
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")
Sep 27 '07 #2

P: 3
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".
Sep 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.