WHERE criteria not act on

  • Thread starter Thread starter owilson
  • Start date Start date
O

owilson

Hi,

I have the following SQL statement:

strSQL = "UPDATE BillingTable " _
& "SET Percentage = Jan " _
& "WHERE YearMonth = YearMonth;"

It works except that it updates all the records, not just
the ones where YearMonth = YearMonth.

Any suggestions would be greatly appreciated.
 
owilson said:
Hi,

I have the following SQL statement:

strSQL = "UPDATE BillingTable " _
& "SET Percentage = Jan " _
& "WHERE YearMonth = YearMonth;"
Click to expand...

owilson,

This Clause -->> WHERE YearMonth = YearMonth

*Always* evaluates True on all rows. This is because YearMonth is
always equal to itself, and therefore, always matching. Which is why
all rows are UPDATEed.


Sincerely,

Chris O.
 
Hi,

I have the following SQL statement:

strSQL = "UPDATE BillingTable " _
& "SET Percentage = Jan " _
& "WHERE YearMonth = YearMonth;"

It works except that it updates all the records, not just
the ones where YearMonth = YearMonth.

Any suggestions would be greatly appreciated.
Click to expand...

As Chris says, this is doing exactly what you're asking it to do:
updating every record where the value in the field yearmonth is equal
to itself!

What value of YearMonth do you want to select for updating? What's the
datatype of YearMonth, and where is your code getting the value?

Also, what do you want to set Percentage to? As it is, it will set it
to the value in the field BillingTable.Jan if there is such a field,
and give you an error if there's not. Is Jan a variable in your VBA
code, a form reference, or what?

John W. Vinson[MVP]
 
Gentlemen,

Thank you for the help. If either one of you would like
to send me a 2 x 4, I'll gladly hit myself over the head
with it. (most times I don't mind being an idiot, but
sometimes it is embarrassing)

Thanks again,

Owen Wilson
 
Owilson said:
Gentlemen,

Thank you for the help. If either one of you would like
to send me a 2 x 4, I'll gladly hit myself over the head
with it. (most times I don't mind being an idiot, but
sometimes it is embarrassing)

Thanks again,

Owen Wilson
Click to expand...

Owen,

No worries.

As far as the 2 x 4 is concerned, an electronic version shall have to
do.

<2 x 4 />


Sincerely,

Chris O.
 
Gentlemen,

Thank you for the help. If either one of you would like
to send me a 2 x 4, I'll gladly hit myself over the head
with it. (most times I don't mind being an idiot, but
sometimes it is embarrassing)
Click to expand...

Happens to all of us!

Glad it was a simple fix (once you saw past all those dratted trees
which were blocking the view of the forest...)

John W. Vinson[MVP]
 
Back
Top