Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameterized queries used in SQL statements (Access 2002)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameterized queries used in SQL statements (Access 2002)

    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 & "))[img]/forums/images/smilies/wink.gif[/img]) 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 & "))[img]/forums/images/smilies/wink.gif[/img]) 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];"

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameterized queries used in SQL statements (Access 2002)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •