Results 1 to 15 of 15
  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

    Error: Invalid procedure call or argument (2000)

    I'm attempting to modify a query's criteria in the after update event of a list box. I have this working quite successfully in one of my databases, in this one... kaputz. It gives the subject error, highlighting the line:

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

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/shrug.gif border=0 alt=shrug width=39 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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error: Invalid procedure call or argument (2000)

    Where is the condition in your HAVING clause ?
    strSQL = strSQL & "HAVING (" & Left(strTemp, Len(strTemp) - 4) & " Here should be a = or <> to something )" & ";"
    Francois

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

    Re: Error: Invalid procedure call or argument (2000)

    Do you expect us to guess what strTemp contains? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error: Invalid procedure call or argument (2000)

    In addition to Francois's suggestion, make sure the resulting SQL string has a space between the end of the existing SQL and the "Having" expression and that there is no stray semicolon in the previously existing SQL.
    Charlotte

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

    Re: Error: Invalid procedure call or argument (2000)

    Francois,
    The condition for the having is included in strTemp... Here's the meat of it:

    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As QueryDef

    strSQL = "SELECT qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[AE],0)) AS AE, Val(NZ([QRYLOCBYCO].[CIV],0)) AS CIV, Val(NZ([QRYLOCBYCO].[Total Of SSN],0)) AS [Total Of SSN] FROM qryLocationCompany LEFT JOIN qryLocByCo ON (qryLocationCompany.LOCATION = qryLocByCo.Location) AND (qryLocationCompany.COMPANY = qryLocByCo.Company) GROUP BY qryLocationCompany.LOCATION, qryLocationCompany.COMPANY, Val(NZ([QRYLOCBYCO].[AE],0)), Val(NZ([QRYLOCBYCO].[CIV],0)), Val(NZ([QRYLOCBYCO].[Total Of SSN],0)) "

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

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


    CurrentDb.QueryDefs("qryReportALL").SQL = strSQL
    ____________________________
    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

  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: Error: Invalid procedure call or argument (2000)

    Hans,
    Having received this bit of code from you, and seeing that it is working properly in another database, I thought it would have to do with the query's properties, just a setting or something I overlooked.... I didn't think it was necessary. <img src=/S/sorry.gif border=0 alt=sorry 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: Error: Invalid procedure call or argument (2000)

    > Having received this bit of code from you

    Are you sure? I wouldn't do it like that.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error: Invalid procedure call or argument (2000)

    Put a break point on the line :
    CurrentDb.QueryDefs("qryReportALL").SQL = strSQL
    (by pressing F9 when the cursor is in the line)
    When the program stop, go to the immediate window (CTRL-g) and type :
    ? strSQL
    and look carefully if the sql string is OK.
    If you want, post it.
    Francois

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

    Re: Error: Invalid procedure call or argument (2000)

    Sorry, I suppose you wouldn't, but Mark Liquorman would <img src=/S/grin.gif border=0 alt=grin width=15 height=15>...

    This is infuriating me....I tried reading through the SQL after using the break and everything looked fine. Except it doesn't have the HAVING statement....
    When I hover over strTemp, get the smart tag strTemp = ""

    <img src=/S/blowup.gif border=0 alt=blowup width=60 height=60>
    ____________________________
    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

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error: Invalid procedure call or argument (2000)

    For Each varItem In Me.ActiveControl.ItemsSelected
    When you run the code, is the listbox still the ActiveControl ?
    How to you call the code ?
    Francois

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

    Re: Error: Invalid procedure call or argument (2000)

    This would happen if no items were selected in the list box.

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

    Re: Error: Invalid procedure call or argument (2000)

    Something I probably should have said earlier....I get the error when I select an item from the list box...
    ____________________________
    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

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

    Re: Error: Invalid procedure call or argument (2000)

    Francois,
    Yes, when I run the code, the list box is still the ActiveControl...the code is called in the list box's After Update event. All is well and good until the code gets up to the original highlighted line. I don't understand why it would give an error. This is *exactly* the same as my other database....
    ____________________________
    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

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error: Invalid procedure call or argument (2000)

    Is the Multi Select property from the listbox set to Simple or Extended ?
    If it is set to None, there will be nothing in the ItemsSelected.
    Francois

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

    Re: Error: Invalid procedure call or argument (2000)

    Gosh Francois...thank you so much... That was it. I figured I was missing a property somewhere, since the code worked elsewhere. Thanks to you and Hans and Charlotte for looking in <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    ____________________________
    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
  •