Parameterized queries used in SQL statements (Access 2002)

Topic

New Reply

This one is beyond me (like so many things seem to be!)

I have attached a query that I need to convert to an SQL statement to use in code. The reason I need to use SQL is because it is designed to make a temporary table and I need to have control over the name of the table duing runtime. If there is another way to do this, please let me know.

Getting the SQL was easy, but converting it into something useful in VBA code is another matter. After some tweaking (changing double quotemarks to single, splitting lines and so forth), I’ve come up with what you will find in the attached file. Open the .mdb and everything should be self explanatory. You will see the query as it looks and works in the Query Editor. Check out the SQL for the Query (!!). Then click on the “Run SQL button” to see the error I’m getting.

By far, this most complex query I’ve designed. There may have been simpler way to do this, but this is what I came up with. If anyone has the time to look closer into this, it would be greatly appreciated.

Reply | Quote

Replies

By inserting a line MsgBox strSQL above the line with CurrentDb.Execute, you can inspect the SQL string. There are two problems:
– The first part is bracketed incorrectly.
– There is no space between the table name and FROM.
The following seems to work:
strSQL = “SELECT ([MonthName] & ‘, ‘ & [DRCCYY] AS [Month]), (([DRCCYY]-2000)*12)+[DRMNTH] AS MonthNum, ” & _
“Sum(tblData.DRTBEN) AS [Total Benefit Paid], Sum(tblData.DRPFAM) AS [Total Family Count] ” & _
“INTO ” & strTableName & _
” FROM tblData INNER JOIN tblMonthNames ON tblData.DRMNTH = tblMonthNames.MonthNum ” & _
“WHERE (((String((5-Len([DRGRP#])),’0’) & [DRGRP#] & ‘ ‘ & String((3-Len([DRGSUB])),’0’) ” & _
“& [DRGSUB] & ‘ ‘ & String((5-Len([DRGLOC])),’0’) & [DRGLOC] & ‘ ‘” & _
“& String((5-Len([DRPOOL])),’0’) & [DRPOOL]) ” & _
“In (SELECT tblReportItem.item FROM tblReportItem WHERE ” & _
“(((tblReportItem.itemType)=0 and tblReportItem.ReportID= ” & _
Forms!frmReport.cboReportList & “))) AND ” & _
“(((([DRCCYY]-2000)*12)+[DRMNTH]) ” & _
“Between ” & [Forms]![frmReport].[cboMonthEnd].[Value] – 12 & ” And ” & _
[Forms]![frmReport].[cboMonthEnd].[Value] & “)) OR ” & _
“(((String((5-Len([DRGRP#])),’0′) & [DRGRP#]) ” & _
“In (SELECT tblReportItem.item FROM tblReportItem ” & _
“WHERE (((tblReportItem.itemType)=-1 and tblReportItem.ReportID= ” & _
Forms!frmReport.cboReportList & “))) AND (((([DRCCYY]-2000)*12)+[DRMNTH]) ” & _
“Between ” & [Forms]![frmReport].[cboMonthEnd].[Value] – 12 & ” And ” & _
[Forms]![frmReport].[cboMonthEnd].[Value] & “)) ” & _
“GROUP BY [MonthName] & ‘, ‘ & [DRCCYY], (([DRCCYY]-2000)*12)+[DRMNTH] ” & _
“ORDER BY (([DRCCYY]-2000)*12)+[DRMNTH];”

Reply | Quote

Hans, you’re a lifesaver. I’ll try this first thing in the morning. As I’ve said in the past, I need to start paying you by the hour. Thanks for all your help.

Reply | Quote

Hans, you’re a lifesaver. I’ll try this first thing in the morning. As I’ve said in the past, I need to start paying you by the hour. Thanks for all your help.

Reply | Quote

By inserting a line MsgBox strSQL above the line with CurrentDb.Execute, you can inspect the SQL string. There are two problems:
– The first part is bracketed incorrectly.
– There is no space between the table name and FROM.
The following seems to work:
strSQL = “SELECT ([MonthName] & ‘, ‘ & [DRCCYY] AS [Month]), (([DRCCYY]-2000)*12)+[DRMNTH] AS MonthNum, ” & _
“Sum(tblData.DRTBEN) AS [Total Benefit Paid], Sum(tblData.DRPFAM) AS [Total Family Count] ” & _
“INTO ” & strTableName & _
” FROM tblData INNER JOIN tblMonthNames ON tblData.DRMNTH = tblMonthNames.MonthNum ” & _
“WHERE (((String((5-Len([DRGRP#])),’0’) & [DRGRP#] & ‘ ‘ & String((3-Len([DRGSUB])),’0’) ” & _
“& [DRGSUB] & ‘ ‘ & String((5-Len([DRGLOC])),’0’) & [DRGLOC] & ‘ ‘” & _
“& String((5-Len([DRPOOL])),’0’) & [DRPOOL]) ” & _
“In (SELECT tblReportItem.item FROM tblReportItem WHERE ” & _
“(((tblReportItem.itemType)=0 and tblReportItem.ReportID= ” & _
Forms!frmReport.cboReportList & “))) AND ” & _
“(((([DRCCYY]-2000)*12)+[DRMNTH]) ” & _
“Between ” & [Forms]![frmReport].[cboMonthEnd].[Value] – 12 & ” And ” & _
[Forms]![frmReport].[cboMonthEnd].[Value] & “)) OR ” & _
“(((String((5-Len([DRGRP#])),’0′) & [DRGRP#]) ” & _
“In (SELECT tblReportItem.item FROM tblReportItem ” & _
“WHERE (((tblReportItem.itemType)=-1 and tblReportItem.ReportID= ” & _
Forms!frmReport.cboReportList & “))) AND (((([DRCCYY]-2000)*12)+[DRMNTH]) ” & _
“Between ” & [Forms]![frmReport].[cboMonthEnd].[Value] – 12 & ” And ” & _
[Forms]![frmReport].[cboMonthEnd].[Value] & “)) ” & _
“GROUP BY [MonthName] & ‘, ‘ & [DRCCYY], (([DRCCYY]-2000)*12)+[DRMNTH] ” & _
“ORDER BY (([DRCCYY]-2000)*12)+[DRMNTH];”

Reply | Quote