RE: (Reply), Tim Logan, 11-11-2003
by Dave, November 12, 2003 12:41
I wasn't very clear before. My goal is to update a field based on a certain condition. The first select statement was for something else that is not being used so I removed it. The update statements are the guts. In the first update statement, I want to find where the patron does not have enough money. I want to then take that $ amount and shove it back in the value I'm updating the field with. So if johnny has $1.00 on account and should have a minimum of $3.00, I want the "update", based on the WHERE statement to say 'Hello, you only have $1.00 on your account.' When I try to stick the:
SET Alert = Hello, you only have $Meal$ on account.
I get either the first $Meal$ record in the database for all updated records or .000 for all records. I can't get the patrons actual $Meal$ value in SET value for the update statement.
After speaking with Roger:
you can't use an ODBiC variable like that in a multiple-row update, because the "current value" in ODBiC will be plugged into the statement *before* passing the statement to ODBC for execution, so it won't change with each row. But you can use column values in SQL update statements, and for each updated row, the ODBC driver will use the value of that column in that same row. In this case, you do need to use SQL's character string concatenation:
... SET Alert = 'Hello, you only have ' + Format(Meal) + ' on account' ...
(That Format function will convert the numeric column to character data, which is required for the concatenation to work. I think that only works in Access; in SQL Server, you have to use Convert(CHAR,meal).)
Thanks for helping!
Name E-mail optional TopicMessage:
HTTP Link: Link text: