Format a number field on a table or query.

[Anand Budhai]

Hi
I am struggling to format a number field on a table/query to reflect 1 decimal after a number.

eg. 1.3 x R4.00 = R5.60

Field defaults to 1 number without the decimal, which calculates as 1 x R4.00 = R4.00

It would be appreciate if someone can assist me.

Thanks

Anand

 

[jdraw]

How is the field defined in the table? What is the calculation(s) being done with that field, if any?

 

[Anand Budhai]

It is defined as a number and the format set as #.#

The operator has to key in the scale reading (weight), which is calculated as follows

eg. Scale reading 0.7kgs x (R5.00) Amt_Per Kg = Total R3.50

Anand

 

[gemma-the-husky]

currency format would give a fixed number of decimals.
single or double might also - I am not sure.

I take it the "R" comes from your regional settings. It's not "text" is it?

 

[Anand Budhai]

Hi
Yes, the R is the regional settings.

 

[namliam]

R is for the local currency I think? As in Rands or Roebels

 

[The_Doc_Man]

Remember that a field's format applies when displaying a field, but it is always stored in whatever format is "natural" to that data type. Therefore, sometimes when you look at a table's contents, the format won't be applied. Other times it will. And also remember that for reports and forms, the control's format CAN override the field's format.

Therefore, first ask yourself the circumstances for which the format isn't being applied.

 

[Cronk]

The calculated field in a query would be

format([Weight] * [Cost], "\R#.#")

 

[Anand Budhai]

The calculated field in a query would be

format([Weight] * [Cost], "\R#.#")

The calculated field in a query would be

format([Weight] * [Cost], "\R#.#")

Hi

Thanks for your reply. My issue is that when the weight is keyed in, it defaults to 1 digit only. eg 1.7 reflects as 1 only. I need the weight to reflect in 1 decimal point.

Thanks

Anand

 

[theDBguy]

Hi. I think jdraw asked the question, but I'm not sure it was answered. So, if the field is defined as a Number, what kind of number was it? Is it a Long Integer, Single, Double, etc.?

 

[gemma-the-husky]

@thedbquy - it appears to be a calculation
ie qty x currency, but maybe just qty x real number.

@op
you should probably format the text box as currency, then it will display fixed decimals.

you may be able to do that with single or double (thereby avoiding the currency symbol, but I am not sure). The point is that generally a trailing zero in 1.70 is superfluous. The number is 1.7, and any number of following zeros beyond that is not necessary.

you may also be able to format the text box using a specific format string - something like "0.00"

none of this formatting will apply to queries and tables, except that currency will show the required number of decimals, and singles/doubles may do, but I don't know without checking. Without being told otherwise, your number is 1.7, 1.72 or even 1.725.

 

[theDBguy]

Hi Dave. If it was a "calculated" field, then the question becomes, "what was the 'result type'?"

 

[The_Doc_Man]

Also what were the contributing numbers? If both of them were integers, you will always get back an integer for multiplication or addition. Automatic variable promotion only occurs for disparate data types.

 

[Anand Budhai]

Thank you all for your input.
I changed the Field size to double with format #.# and it worked.
Regards
AB

 

[theDBguy]

Thank you all for your input.
I changed the Field size to double with format #.# and it worked.
Regards
AB

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.