update query

[alecgreen]

Hi

I want an update query, that deducts 20% from the value of a field
[SOH], assuming that [SOH] is greater than zero. I also need the
resulting figure to be a whole number. ie not ending in 0.25 or 0.5
etc.

Any help would be greatly appreciated.

Many Thanks

Alec

 

[Marshall Barton]

I want an update query, that deducts 20% from the value of a field
[SOH], assuming that [SOH] is greater than zero. I also need the
resulting figure to be a whole number. ie not ending in 0.25 or 0.5
etc.

Try something like:

UPDATE table
SET SOH = CLng(.8 * SOH)
WHERE SOH Is Not Null
And SOH > 0

But make sure you have a backup of the table before you
run/test the query because you will make a mess of it if you
run the query more than once.

 

[Jerry Whittle]

UPDATE tblCurrency
SET tblCurrency.SOH = Int([SOH]*0.8)
WHERE tblCurrency.SOH >0 ;

 

[John W. Vinson]

Hi

I want an update query, that deducts 20% from the value of a field
[SOH], assuming that [SOH] is greater than zero. I also need the
resulting figure to be a whole number. ie not ending in 0.25 or 0.5
etc.

Any help would be greatly appreciated.

Many Thanks

Alec

UPDATE mytable
SET [SOH] = Fix(0.8 * [SOH])
WHERE [SOH] > 0;

Deducting 20% from 0 will give 0 so the criterion isn't needed, but there's no
point in running a "do nothing" update.