Issue with fixed decimal places

[memfbi]

Hello
I have an update qry to a table with a field defined as Number/Double/Fixed/3 decimal places.
When I view table the field displays as .001, but when I click on the field it displays as.00057. I need to use this field in later calculations to be .001, but in the later calculations it is using .00057.
How can I keep this field at .001?

Thank you

[NeoPa]

Hi there.

Don't confuse how data is displayed with how data is stored. It's a common misconception that trips up many people.

Really you need to ensure that the original UPDATE query is fixed to do what you need but we don't have that available at this time so I'll try to put something together that illustrates the point & the technique and shows how the existing data can be fixed. Post your original query if you'd like us to illustrate what you need more directly. Better still, post that with a fixed version that you think is the solution and we'll confirm or deny & help further if necessary.

UPDATE [YourTable]
SET    [YourField]=Round([YourField],3)

[memfbi]

Hello
Thought I had this working, but not quite. It appears that I need to round up to get the correct answer.... I need the below to return .113, but is returning .112
MGCP10 = 5.00 VOLP00 = 225

UPDATE PGAS000
       LEFT JOIN
       PGASC02
    ON PGAS000.MGC100 = PGASC02.GPRD02
SET    PGAS000.[MGC1$0] = ((Round([MGCP10]/100*[VOLP00]/100,3)))

[NeoPa]

You have hit the Banker's Rounding problem (See Allen Browne's page on rounding - which incidentally is ok to link to as MS MVPs are valid for the relevant MS software).

There's a link on that page to a function you can add to your project if you want Arithmetic Rounding. There may be alternative solutions by now as this page goes back to when Access 2000 was current ;-)