Decimals and rounding

[DBL]

I have a field that holds a unit price. I can't use currency as the data type because the currency type is different for each client. I have another field that specifies the currency type for each department. Up to now I've been using a double format with a 2 decimal places. We now need to increase the decimal places to 4 but don't necessarily want to see all four places if only 2 are required - 1.5000 against 1.50. However if I make the decimals as auto, if I have 1.50 it rounds to 1.5.

Any other way to give a bit of flexibility to this?

Thanks.

Dawn

 

[RV]

I can't use currency as the data type because the currency type is different for each client

The data type "currency" doesn't come with a "currency type"
You think it does cause the amounts all start with "your" currency.
In your table, set the format for your "currency" column to "standard".
Set the number of decimal places to 4

We now need to increase the decimal places to 4 but don't necessarily want to see all four places if only 2 are required

Why, I've never seen anybody bothering 'bout this.
I wouldn't bother if I were you

RV

 

[Advertiser]

If you link your currency types to a number ( of decimal places), when you display your form, use the oncurrent event to reset the number of decial places to diplay on that record.

OK?

 

[DBL]

RV - thanks but if I do this then the number rounds up if the decimal places are set to auto. If I set it to four I'm back to four decimal places if I need them or not.

Advertiser - I'm not sure what you mean? There is not set unit price, each is unique to that order and is regardless of the currency type.

 

[RV]

If I set it to four I'm back to four decimal places if I need them or not

You can set the format to "General", 4 decimals, data type "Currency"

What's wrong using 4 decimals all the time anyway?
It's common practice

You're trying to overcomplicate matters

RV

 

[DBL]

If I set it to four and the unit price is 1.50 it changes to 1.5.

The unit price has to show as it comes from the supplier, correct to the decimal place. If the unit price is 0.315 and there's 10,000 units then every decimal place counts!

 

[pbaldy]

How about a format of:

#.00##

 

[RV]

If the unit price is 0.315 and there's 10,000 units then every decimal place counts!

I could be wrong but won't 10,000*0.315 result in the same outcome as 10,000*0.3150 .......
So what's your point?

Use queries to "show" your data.
In your queries, use the Format function plus a user defined function to round any data you'd like to calculate.

The unit price has to show as it comes from the supplier, correct to the decimal place.

It really doesn't matter whether there will be any "leading zeros".
You're overcomplicating matters

RV

 

[Jon K]

To preserve the number of decimals in the suppliers' unit prices, you can store the unit prices in a text field instead of a numeric field. And you can still return the Amounts using UnitPrice * Quantity.
.