Format number in query

[Hillary]

First, in my query, I am dividing Total Value by Ending Balance. Because parts were bought at different cost, some the values returned are 18 places to the right of the decimal. (example: .013333333333333333) In the Field Properties I have set the Format to General Number and the Decimal Places to 2 but it has no effect on how the query is displayed. How do I round to a 2 place decimal?

Secondly, same situation but I have an IF statement where the truepart returns a string and the falsepart returns a calculation. There is no decimal option on the Field Properties. How do I round to a 2 place decimal?

Thanks for your help.

Hillary

 

[KenReay]

Hi

In your query put a calculated column:

Result:Format(Total Value/Ending Balance,"########.##&quot

or possibly

Result:CCur(Total Value/Ending Balance)

Same idea (ie Format) should work for your IIF situation Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MichaelRed]

You cannot "Format" a value with "General Number" Select a differnt type (of number).

Another consideration MIGHT be where the query results are used. If it is for a form/report field, it makes more sense (to me) to do the formatting in the destination object, as opposed to the query itself.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Hillary]

Thanks guys for your help.

I changed the Format from General Number to Standard and the query now reads the Decimal Places in the Field Properties.

On the other issue, I was not successful using Format(Total Value/Ending Balance,"########.##&quot. I kept getting an error in the date format. When I use the CCur(Total Value/Ending Balance) it defaults to 4 decimal places. Any idea on how to round to a whole number? Do you know if there is a list of these codes published anywhere?

Thanks,
Hillary