UPDATE syntax

[owilson]

Hi,

Me again.

I want to update a record, Percentage, in table named
BillingTable.

The [new value] will come from a variable based on the
value of a text box on form. My UPDATE is:

strSQL = "UPDATE BillingTable" _
& "SET Percentage = " & strNewPercent & " " _
& "WHERE YearMonth = YearMonth"

'Percentage' doesn't seem to reacting as 'strNewPercent'
and both 'YearMonth''s receive a value per the immediate
window.

Any help will be appreciated beyond belief.

TIA

Owen Wilson

 

[Guest]

I see a couple of problems with your string.

You need a space between BillingTable and SET because when it all concats
then you get "UPDATE BillingTableSET Percentage ..."

If strNewPercent is a string then it needs apostrophes around it.

if yearmonth value is from the form then it needs to be outside the quotes.
if BillingTable's YearMonth is a date then YearMonth needs pound symbols
around it.

strSQL = "UPDATE BillingTable" _
& "SET Percentage = " & strNewPercent & " " _
& "WHERE YearMonth = YearMonth"

so it should look like this:

strSQL = "UPDATE BillingTable " _
& "SET Percentage = '" & strNewPercent & "' " _
& "WHERE YearMonth = #" & YearMonth & "#"

If i missed something let me know.
good luck!

 

[Rob Parker]

Assuming that YearMonth is a variable (from either a recordset or a form
control),
the syntax for the WHERE clause needs to use its value.

Currrently, strSQL will update all records (if you remove the obvious errors
which prevent the version you posted from running at all!), since the
expression YearMonth =YearMonth is always true.

Try amending your strSQL to the following:
strSQL = "UPDATE BillingTable" _
& " SET Percentage = " & strNewPercent & " " _
& "WHERE YearMonth = " & YearMonth & ";"

Note the space before SET and the closing semicolon.

I also suggest that NewPercent should both a number, rather than a string.
(I'm assuming that Percentage in BillingTable is a number; if it isn't it
also should be.)

HTH,

Rob

 

[John Vinson]

Hi,

Me again.

I want to update a record, Percentage, in table named
BillingTable.

The [new value] will come from a variable based on the
value of a text box on form. My UPDATE is:

strSQL = "UPDATE BillingTable" _
& "SET Percentage = " & strNewPercent & " " _
& "WHERE YearMonth = YearMonth"

'Percentage' doesn't seem to reacting as 'strNewPercent'
and both 'YearMonth''s receive a value per the immediate
window.

Is there a field named YearMonth in BillingTable? What's its datatype?
Where is the value of YearMonth for the record that you want to update
obtained? As written, this will compare the value of [YearMonth] in
the BillingTable to itself, and if it is equal to itself (which of
course it will always be) it should run the update.

Note that your phrase "update a record, Percentage," is confusing: you
might have a *field* named Percentage, but certainly not a record;
records don't have names. Also, you are missing a blank between
BillingTable and SET - try editing your first line to

"UPDATE BillingTable " _

so the SQL clause becomes

UPDATE BillingTable SET....

rather than

UPDATE BillingTableSET ...

John W. Vinson[MVP]