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" <(E-Mail Removed)> wrote in message
news:25ad01c50baf$226824c0$(E-Mail Removed)...
> Hi,
>
> I have the following SQL statement:
>
> strSQL = "UPDATE BillingTable " _
> & "SET Percentage = Jan " _
> & "WHERE YearMonth = YearMonth;"

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.



>
> It works except that it updates all the records, not just
> the ones where YearMonth = YearMonth.
>
> Any suggestions would be greatly appreciated.

On Sat, 5 Feb 2005 10:18:47 -0800, "owilson"
<(E-Mail Removed)> wrote:

>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.

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
>-----Original Message-----
>On Sat, 5 Feb 2005 10:18:47 -0800, "owilson"
><(E-Mail Removed)> wrote:
>
>>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.
>
>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]
>.
>

"Owilson" <(E-Mail Removed)> wrote in message
news:1d4c01c50be7$e0703c90$(E-Mail Removed)...
> 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

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.

On Sat, 5 Feb 2005 17:04:58 -0800, "Owilson"
<(E-Mail Removed)> wrote:

>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)

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]