Trouble with SQL (Access 2003)

Topic

New Reply

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.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’ve attached an image of how I have the table setup if this helps.

Thank you for any help.
Drew

Reply | Quote

Replies

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.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));”

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.Form!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?)

Reply | Quote

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.Form!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

Reply | Quote

There is no field named Line in the recordset. Try

CountIt = rst!MaxOfLine

Reply | Quote

Works
Thanks

Reply | Quote