Help with SQL string that works but not in VBA

Question

Help with SQL string that works but not in VBA

Greymyst

SSC Rookie

Points: 31
More actions
April 25, 2002 at 9:19 am

#157708

Hello Out There
I am working in Access trying to build a SQL string that will pull together 2 preset queries and add them to a results table.
I have used VBA to add in names based on day so that the code can just loop through the same query-no such luck 🙁
I get the message "Runtime Error3061 Too few parameters" when I run it through VBA. If I run the SQL string directly. it works fine but not when in code. ARGHHHHHH
Please if anyone can help, before my boss finds out how much time this has taken, I would be most happy 🙂
here is the code:
sql1 = "SELECT Call_offer_by_bux_" & dayx & ".BUXNO, Call_offer_by_bux_" & dayx & ".start_time, [Call_offer_by_bux_" & dayx & "]![SumOfn_offer]+[Conting_by_bux_" & dayx & "]![SumOfAbandoned] AS Calls " & _
"FROM Call_offer_by_bux_" & dayx & " INNER JOIN Conting_by_bux_" & dayx & " ON (Call_offer_by_bux_" & dayx & ".BUXNO = Conting_by_bux_" & dayx & ".BUXNO) AND (Call_offer_by_bux_" & dayx & ".date = Conting_by_bux_" & dayx & ".date) AND (Call_offer_by_bux_" & dayx & ".start_time = Conting_by_bux_" & dayx & ".start_time);"
Set dbs1 = CurrentDb.OpenRecordset(sql1, dbOpenDynaset)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Sorry can you post a clean example of what did work in SQL code. I just want to compare as I don't see the issue right off.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

kaeg Old Hand Points: 378 More actions · Answer 2

You have to supply the dayx (I guess it's Integer) to the code and make the sql 1 as a parametered sql like this

sql1 = "PARAMETERS dayx Short; " _

" SELECT Call_offer_by_bux_" & dayx & ".BUXNO, ...

Set qdf = dbs1.QueryDefs(sql1)

qdf.Parameters!dayx = 4

Set rst = qdf.Openrecordset()

HTH

Tim K.

Greymyst SSC Rookie Points: 31 More actions · Answer 3

Thanks for the replies 🙂

Antares the original SQL was

SELECT Call_offer_by_bux_mon.BUXNO, Call_offer_by_bux_mon.start_time, [Call_offer_by_bux_mon]![SumOfn_offer]+[Conting_by_bux_mon]![SumOfAbandoned] AS Calls

FROM Call_offer_by_bux_mon INNER JOIN Conting_by_bux_mon ON (Call_offer_by_bux_mon.BUXNO = Conting_by_bux_mon.BUXNO) AND (Call_offer_by_bux_mon.date = Conting_by_bux_mon.date) AND (Call_offer_by_bux_mon.start_time = Conting_by_bux_mon.start_time);

This runs well but i am using an array to feed in different days(_mon to _tue ect) for each query as it does a DO LOOP, I don't think it picks up the last 2 joins as joins .

I tried putting the [] brackets that Access likes in but then it gives an error that the join is too complex. I may have to give up and add even more queries to their database.

Thanks for your time:)

I have figured out how to do it now. If anyone else has the same problem you have to use the QueryDefs collection to create a temporary query in access that you delete as you rewrite it with code using the new variables.

Edited by - Greymyst on 04/29/2002 07:41:44 AM