Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting a WHERE statement into a query from code (2000)

    I've been pulling bits and pieces from code I've used elsewhere in my database, attempting to modify a where statement in one of my queries....unfortunately I have items after the WHERE statement, I think that's where I'm getting an error. Then I tried adding two more lines, and the first one gave me another error. Code below

    <pre>Private Sub lstLocations_AfterUpdate()
    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef

    strSQL = "TRANSFORM Count(tblPersonnel.SSN) AS CountOfSSN SELECT tblPersonnel.LOCATION, " & _
    "tblPersonnel.COMPANY, Count(tblPersonnel.SSN) AS Total FROM tblPersonnel GROUP BY " & _
    "tblPersonnel.LOCATION, tblPersonnel.COMPANY PIVOT tblPersonnel.RANK_STATUS In " & _
    "(""MO"",""ME"",""NO"",""NE"",""AO"",""AE"",""CIV" ") "

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & "((tblPersonnel.Company) = '" & Me.ActiveControl.ItemData(varItem) & " ') Or "
    Next

    strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) & ")" & ";"""


    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">db.QueryDefs.Delete "qryMorningReportActualNumbers"</span hi> <font color=448800>'Error Object Variable or With block variable not set.</font color=448800>
    Set qry = db.CreateQueryDef("qryMorningReportActualNumbers", strSQL)

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">CurrentDb.QueryDefs("qryMorningReportActualN umbers").SQL = strSQL</span hi> <font color=448800>'Syntax error in TRANSFORM statement.</font color=448800>

    End Sub</pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Inserting a WHERE statement into a query from code (2000)

    The order of the elements in an SQL statement is fixed:

    TRANSFORM ...
    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING ...
    ORDER BY ...
    PIVOT ...

    (not all element have to occur)

    So try
    <code>
    strSQL = "TRANSFORM Count(tblPersonnel.SSN) AS CountOfSSN SELECT tblPersonnel.LOCATION, " & _
    "tblPersonnel.COMPANY, Count(tblPersonnel.SSN) AS Total FROM tblPersonnel "

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & "tblPersonnel.Company = '" & Me.ActiveControl.ItemData(varItem) & "' Or "
    Next varItem

    strSQL = strSQL & "WHERE (" & Left(strTemp, Len(strTemp) - 4) & ") " & _
    "GROUP BY tblPersonnel.LOCATION, tblPersonnel.COMPANY PIVOT tblPersonnel.RANK_STATUS In " & _
    "(""MO"",""ME"",""NO"",""NE"",""AO"",""AE"",""CIV" ")"
    </code>

    This way, the WHERE part is inserted in the right location.

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

    Re: Inserting a WHERE statement into a query from code (2000)

    Oh, and you forgot

    Set db = CurrentDb

    before using db.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting a WHERE statement into a query from code (2000)

    Hans, that works beautifully, thank you.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Inserting a WHERE statement into a query from code (2000)

    FWIW, you might find it easier to user apostrophes embedded within strings; easier to read and easier to write.

    Instead of: "(""MO"",""ME"",""NO"",""NE"",""AO"",""AE"",""CIV" ") "
    Use: "('MO', 'ME', 'NO', 'NE', 'AO', 'AE', 'CIV')"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting a WHERE statement into a query from code (2000)

    Mark, <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> for the top tip! Nothin a little find and replace won't fix <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Inserting a WHERE statement into a query from code (2000)

    And you can make the WHERE clause a little bit more efficient by using IN (...):
    <code>
    strSQL = "TRANSFORM Count(SSN) AS CountOfSSN SELECT LOCATION, " & _
    "COMPANY, Count(SSN) AS Total FROM tblPersonnel "

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & ",'" & Me.ActiveControl.ItemData(varItem) & "'"
    Next varItem

    strSQL = strSQL & "WHERE COMPANY IN (" & Mid(strTemp, 2) & ") " & _
    "GROUP BY LOCATION, COMPANY PIVOT RANK_STATUS IN " & _
    "('MO','ME','NO','NE','AO','AE','CIV')"
    </code>
    If many items have been selected, the construction with OR leads to a very long SQL string because the field name has to be repeated each time. If you use IN (...), you only have to specify the field name once.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting a WHERE statement into a query from code (2000)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> again!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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