asked on

SQL Update String

Hello,

This portion of my code keeps telling me it is missing a parameter. Any ideas?

PLU = "MR9H"
  SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo" & x & " = " & PLU
  db.Execute SQL

Open in new window

prachwal

maybe You don't Have field Promo in table rtblSKURptHeader

vmccune

ASKER

Nope. The field in the table is promo1,2,3 ect. the x variable then assigns it to promo1, promo2 etc. That portion works fine.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

You're missing a space after Promo.

Corrected:
SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo " & x & " = " & PLU

mbizup

In explanation, your resulting SQL would be:

UPDATE rtblSKURptHeader SET rtblSKURptHeader.PromowhateverTheValueOfXis = ...

The Parameter error is occuring because with X concatenated onto Promo, rtblSKURptHeader.PromowhateverTheValueOfXis becomes an unrecognized fieldname

Rey Obrero (Capricorn1)

miriam ,
<You're missing a space after Promo>
if you do that, the resulting field name will be Promo 1..

mbizup

D'oh...

I should have read more carefully. Bed time.

Rey Obrero (Capricorn1)

the problem lies in the fact that the variable PLU is atext type type of data and being used as a Number type of data in the SQL statement

PLU = "MR9H"
SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo" & x & " = " & PLU
db.Execute SQL

this problem was addressed in http:#a24234112 with

PLU = "MR9H"
SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo" & x & " = '" & PLU & "'"

db.Execute SQL

vmccune

ASKER

Correct String
SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo" & x & " = '" & PLU & "';"

capricorn1 String
SQL = "UPDATE rtblSKURptHeader SET rtblSKURptHeader.Promo" & x & " = '" & PLU & "'"

Problem solved.

Rey Obrero (Capricorn1)

vmccune,
you don't even need that { ; } in that statement

mbizup

I concur.

Since the Promo fields are text, the PLU variable needs to be delimited with single quotes.

The only difference between the two statements posted in http:#a24245625 is the trailing semicolon, which is optional.

I recommend accepting http:#a24234112

vmccune

ASKER

Thank you for helping me.