Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble with SQL (Access 2003)

    I've built the following SQL in Access' query builder and seem to work fine when executing from the builder. When I try to place it into a script, I get an Error (Run-Time Error 13). I think I'm getting this error because what I'm trying to run is a parameter query, but I'm not quite sure how to fix it. I've tried a few things but still get the error.

    SELECT T_AgileData_Parsed.Item_ID, Max(T_AgileData_Parsed.Line) AS MaxOfLine
    FROM T_AgileData_Parsed
    WHERE (((T_AgileData_Parsed.Line) Like Int(Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.F orm!line) & "*"))
    GROUP BY T_AgileData_Parsed.Item_ID
    HAVING (((T_AgileData_Parsed.Item_ID) Like Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID));

    I've attached an image of how I have the table setup if this helps.

    Thank you for any help.
    Drew

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

    Re: Trouble with SQL (Access 2003)

    I'm not sure what you mean by when you place it into a script, but if you're placing it in VBA Code, you need to break the code, as such

    "SELECT T_AgileData_Parsed.Item_ID, Max(T_AgileData_Parsed.Line) AS MaxOfLine " & _
    "FROM T_AgileData_Parsed " & _
    "WHERE (((T_AgileData_Parsed.Line) Like Int(Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.F orm!line) & "*")) " & _
    "GROUP BY T_AgileData_Parsed.Item_ID " & _
    "HAVING (((T_AgileData_Parsed.Item_ID) Like Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID));"
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with SQL (Access 2003)

    Jermey,

    Yes...I meant placing the SQL into VBA code (so used to calling it a script).
    I tried this and still get the same error.

    Thanks

  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: Trouble with SQL (Access 2003)

    How exactly are you running this? You need to use DoCmd.RunSQL

    Why don't you save the query as an object, and use DoCmd.OpenQuery "qryQUERYNAME"
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Trouble with SQL (Access 2003)

    When you place the sql into VBA you can't just include the reference to the form. Instead the value from the form needs to resolved and the value itself included in the sql.

    sql = "SELECT T_AgileData_Parsed.Item_ID, Max(T_AgileData_Parsed.Line) AS MaxOfLine " & _
    " FROM T_AgileData_Parsed " & _
    " WHERE (((T_AgileData_Parsed.Line) Like Int(" & Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! line & ") & "*")) " & _
    " GROUP BY T_AgileData_Parsed.Item_ID " & _
    " HAVING (((T_AgileData_Parsed.Item_ID) Like " & Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID & "))"

    Include msgbox sql on the next line to see what happens.
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with SQL (Access 2003)

    I tried tried this and get a 'Type Mismatch' error.
    When commenting out ...
    & _
    "WHERE (((T_AgileData_Parsed.Line) Like Int(" & Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Line & ") & " * "))" & _
    "GROUP BY T_AgileData_Parsed.Item_ID " & _
    "HAVING (((T_AgileData_Parsed.Item_ID) Like " & Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID & "))"

    I get the msgbox with the sql statement (minus WHERE on).

    Thanks

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

    Re: Trouble with SQL (Access 2003)

    Try this, it adds quotes around the string values:

    sql = "SELECT T_AgileData_Parsed.Item_ID, Max(T_AgileData_Parsed.Line) " & _
    "AS MaxOfLine FROM T_AgileData_Parsed WHERE " & _
    "T_AgileData_Parsed.Line Like " & Chr(34) & _
    Int(Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.F orm!line ) & _
    "*" & Chr(34) & " GROUP BY T_AgileData_Parsed.Item_ID " & _
    " HAVING T_AgileData_Parsed.Item_ID Like " & Chr(34) & _
    Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID & Chr(34)

    (BTW why do you have Like in the HAVING part without a wildcard?)

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with SQL (Access 2003)

    Hans,
    I can't recall why the Like statement was in the HAVING part. It must of been one of the many things I tried when attempting to make the SQL work and forgot to change it back.
    Looks like the SQL works great (THANKS!!!) however, when I set this SQL to a recordset (rst) and try to retrieve the Line data, I get a msgbox with a 3265 error. Here is the code I use...

    SQLQuery = "SELECT T_AgileData_Parsed.Item_ID, Max(T_AgileData_Parsed.Line) " & _
    "AS MaxOfLine FROM T_AgileData_Parsed WHERE " & _
    "T_AgileData_Parsed.Line Like " & Chr(34) & _
    Int(Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.F orm!Line) & _
    "*" & Chr(34) & " GROUP BY T_AgileData_Parsed.Item_ID " & _
    " HAVING T_AgileData_Parsed.Item_ID Like " & Chr(34) & _
    Forms!F_DataReview_SI!F_AgileData_Parsed_sDS.Form! Item_ID & Chr(34)

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(SQLQuery)

    rst.MoveFirst
    Countit = rst!Line

    Do you know if I'm doing something wrong? My end goal is to pull out the max number (Fractional) from what line integer has focus on my form. Maybe I'm taking the wrong approach???

    Thanks,
    Drew

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

    Re: Trouble with SQL (Access 2003)

    There is no field named Line in the recordset. Try

    CountIt = rst!MaxOfLine

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble with SQL (Access 2003)

    Works
    Thanks

Posting Permissions

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