Run-time Error 3421 1

[Rjc8513]

I'm stumped. Trying to create a make-table query in code. When I run the code below, I get 'Run-time error 3421, Data type conversion error'.

When I first create the QueryDef in code using the same parameters and criteria, and then run the created query, it works just fine. What am I doing wrong?!

dbs.Execute "mqry PD's by PAY LOC", _
"SELECT [tbl DATA].[WKNO], [tbl DATA].[FINANCE], " & _
"[tbl LOC DATA].[PAY LOCATION] AS PAYLOC, [tbl DATA].[FONCODE], " & _
"[tbl DATA].[FONCAT], Val(Format(Sum([tbl DATA].[ACT RES CURB]+" & _
"[tbl DATA].[ACT RES NDCBU]+[tbl DATA].[ACT RES CENT]+" & _
"[tbl DATA].[ACT RES OTHER]+[tbl DATA].[ACT BUS CURB]+" & _
"[tbl DATA].[ACT BUS NDCBU]+[tbl DATA].[ACT BUS CENT]+" & _
"[tbl DATA].[ACT BUS OTHER]),'0')) AS ACTUAL " & _
"INTO [tbl DATA by PAY LOC] " & _
"FROM [tbl DATA] LEFT JOIN [tbl PAY LOC DATA] " & _
"ON [tbl DATA].[DELIVERY ZIP]=[tbl PAY LOC DATA].[5-DIGIT ZIP] " & _
"WHERE ((([tbl DATA].[CARRIER ROUTE]) Like 'C*')) " & _
"GROUP BY [tbl DATA].[WKNO], [tbl DATA].[FINANCE], " & _
"[tbl PAY LOC DATA].[PAY LOCATION],[tbl DATA].[FONCODE], " & _
"[tbl DATA].[FONCAT] " & _
"HAVING Val(Format(Sum([tbl DATA].[ACT RES CURB]+" & _
"[tbl DATA].[ACT RES NDCBU]+[tbl DATA].[ACT RES CENT]+" & _
"[tbl DATA].[ACT RES OTHER]+[tbl DATA].[ACT BUS CURB]+" & _
"[tbl DATA].[ACT BUS NDCBU]+[tbl DATA].[ACT BUS CENT]+" & _
"[tbl DATA].[ACT BUS OTHER]),'0'))> 0;"

 

[PHV]

Replace tis:
dbs.Execute "mqry PD's by PAY LOC", _
By this:
dbs.Execute _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rjc8513]

Thanks, PHV! That fixed it.