SQL Access VBA UPDATE table with another table? Too Few Parameters

Question

I am looking to update one of my tables with another table depending on a field condition. I am wondering what the correct way to do this is. I tried to do UPDATE statements with the 2 tables, but everytime it came out with this error: Too few Parameters, Expected 2

My goal: If SOURCING field = O, I want Zip Code to be Origin Postal Code If SOURCING field = D, I want Zip Code to be Dest Postal Code

So as of right now, I am simply doing a LEFT JOIN with a condition. Is this the best way to do it? Or should I have done this with the original INSERT statement somehow?

CurrentDb.Execute "UPDATE Processing" & _
" LEFT JOIN tblImport" & _
" ON Processing.[BATCH_NO] = tblImport.[BATCH_NO]" & _
" SET Processing.[Zip Code] = tblImport.[Origin Postal Code]" & _
" WHERE tblImport.[Sourcing] = O;"

CurrentDb.Execute "UPDATE Processing" & _
" LEFT JOIN tblImport" & _
" ON Processing.[BATCH_NO] = tblImport.[BATCH_NO]" & _
" SET Processing.[Zip Code] = tblImport.[Dest Postal Code]" & _
" WHERE tblImport.[Sourcing] = D;"

I have tried changing the WHERE statement since I am not sure if it should be in quotes, single quotes, not in quotes, etc... but I have come up empty there. Everything else looks correct to me.

Are you sure you should be using a JOIN for the tables? It looks like you could just use a subquery to get the data you want from tblImport instead, since you aren't setting any values in that table. — ZeroK
– ZeroK, Commented May 28, 2013 at 16:32
Don't 'O' and 'D' need to have single quotes around them bc they're string fields? That's probably one of the two "parameters". — Dick Kusleika
– Dick Kusleika, Commented May 28, 2013 at 18:30

Johnny Bones · Accepted Answer · 2013-05-28 21:09:46Z

Here's what I do, you might find this helpful; Set a variable equal to your SQL string, then CurrentDB.Execute the variable. Why is this helpful? Because you can break the code after the variable is set, and then copy the SQL into a new query and see what it's complaining about. :o)

Dim tmpUpdate as string

tmpUpdate = "UPDATE Processing" & _
" LEFT JOIN tblImport" & _
" ON Processing.[BATCH_NO] = tblImport.[BATCH_NO]" & _
" SET Processing.[Zip Code] = tblImport.[Origin Postal Code]" & _
" WHERE tblImport.[Sourcing] = O;"

CurrentDB.Execute tmpUpdate

Set a breakpoint on the "tmpUpdate =" line, and then run the code. When it hits the breakpoint, press F8 to step to the next line. In the Immediate window, type "?tmpUpdate" (without the quotes) and see what it thinks the variable is equal to. Then, go to the database, create a new query and go to the SQL of the query. Copy and paste the SQL from the Immediate window and try running the query. If it pukes, go to the Design view and see if you can see anything that doesn't look right.

Collectives™ on Stack Overflow

SQL Access VBA UPDATE table with another table? Too Few Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related