Prev: Repetitive Results
Next: return product with maximum price
From: alecgreen on 25 Mar 2010 11:39
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
From: Marshall Barton on 25 Mar 2010 12:20
alecgreen wrote:
>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.

--
Marsh
MVP [MS Access]
From: Jerry Whittle on 25 Mar 2010 12:46
UPDATE tblCurrency
SET tblCurrency.SOH = Int([SOH]*0.8)
WHERE tblCurrency.SOH >0 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"alecgreen" wrote:

> 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
> .
>
From: John W. Vinson on 25 Mar 2010 12:59
On Thu, 25 Mar 2010 08:39:26 -0700 (PDT), alecgreen <alecgreen(a)hotmail.co.uk>
wrote:

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

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Repetitive Results
Next: return product with maximum price