'Invalid argument' error when sql run in Access VB. (1 Viewer)

Hi,

I get an 'Invalid argument' error message when I run the 'strSQL' part of my vb code (in Access VB). However, the query seems to run ok as a straight sql query in Access. Is there a better way of structuring sql codes executed from within VB? I am hoping that someone would be kind enough to point the errors to a new user Thanks.

My vba below:-

Firstly: I declare all variables
Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim strSQL As String
Dim str, str1, str2, str3, str4, str5, str6, str7, str8 As String

Secondly: I set the values
str1 = " And "
str3 = " OR "
str4 = "'" & "PD*" & "'"
str5 = "'" & "TN*" & "'"
str8 = ")"

Set WorkBase = OpenDatabase("C:\mydb.mdb")

strSQL = "SELECT Machine_Prod.ToolNo, Sum(Machine_Prod.SpringQty) AS qty FROM Machine_Prod" & _
" WHERE ((Machine_Prod.ToolNo Like" & str4 & str8 & str1 & "(Machine_Prod.EndofToolLife=0))" & str3 & "((Machine_Prod.ToolNo Like" & str5 & str8 & str1 & "(Machine_Prod.EndofToolLife)=0)" & " GROUP BY (Machine_Prod.ToolNo), (Machine_Prod.ToolNo)" & _
" HAVING ((Machine_Prod.ToolNo Like" & str4 & str8 & str1 & "((Sum(Machine_Prod.SpringQty))>=15000))" & str3 & "((Machine_Prod.ToolNo Like" & str5 & str8 & str1 & "((Sum(Machine_Prod.SpringQty))>=40000));"

Set WorkRS1 = WorkBase.OpenRecordset(strSQL, dbOpenDynaset)

Hi,

Can you provide the straight query which works please.

Also, I wouldn't use all the str1, str2 etc.
Just put them into the strSQL directly.
There is also an awful lot of parenthesis in your SQL (I'm fairly certain Access put them in for you but Access is terrible at doing that.)
You can also take out the 'Machine_Prod.' as you are only looking at 1 table and there won't be any conflict in names i.e. CustomerID in the Customer table and the Sales table.

I haven't looked too hard at the query at the minute, but try the above and you should be able to read it a lot easier, see a rough idea below:


Post the SQL which works and I will have look at it.
But see if the above helps too.

Edit: I've just realised you don't have any spaces between - like and 'PD*'/'TN*'

Cheers,

Thanks Ian

I have fixed the 'missing space' in my code. Also, simplified my code as per your suggestion. It works brilliantly!!!

This forum is cracking - Cheers mate!!