Update Query calculation help 1

[jazminecat]

Hi - I have a table with many fields, and I am working with fields ProdScrapRate and ProdYield. I imported the data for Yield from an excel spreadsheet.
Both ProdScrapRate and ProdYield are percentages.
The data field size is decimal,
format=percent,
scale and decimal places set to 2.
Scraprate is currently zeroes.

I need to run an update query to calculate the Prodscraprate - it's just 100 - Prodyield,
so if the yield is 98% the scraprate is 2%.
The ProdYield will eventually be a deleted field, I just need to do this calculation and then I can delete it.

However, I can't get an update query to produce any results. The sql currently reads:

UPDATE tblProducts SET tblProducts.ProdScrapRate = [ProdYield]-100;

this just gives me zero percentages in my ProdScrapRate field.


I just built it in the design window, I didn't write the SQL.

I have also tried it as

UPDATE tblProducts SET tblProducts.ProdScrapRate = 100-[ProdYield];

which also gives me zeros.

Thanks in advance for your advice!

 

[PHV]

And this ?
UPDATE tblProducts SET tblProducts.ProdScrapRate = 1-[ProdYield]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

scrap rate still appears as 0.00%

 

[PHV]

Have you executed the update query with the ! toolbar icon ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

no, I am just looking at it with the view panel...should I run it? Would that give me different results?

 

[PHV]

The view panel shows you only which rows will be affected.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

You SO rock! Thank you so much! It worked, of course, and I learned something new. Have a star! And again, thank you!! Such a small thing but it saved me hours of data entry.