Format a number field on a table or query. (1 Viewer)

Anand Budhai

New member
Local time
Today, 15:13
Joined
Jan 30, 2020
Messages
6
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

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Jan 23, 2006
Messages
15,364
How is the field defined in the table? What is the calculation(s) being done with that field, if any?
 

Anand Budhai

New member
Local time
Today, 15:13
Joined
Jan 30, 2020
Messages
6
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
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Sep 12, 2006
Messages
15,614
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?
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:13
Joined
Aug 11, 2003
Messages
11,696
R is for the local currency I think? As in Rands or Roebels
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:13
Joined
Feb 28, 2001
Messages
27,001
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

Registered User.
Local time
Tomorrow, 00:13
Joined
Jul 4, 2013
Messages
2,770
The calculated field in a query would be

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

Anand Budhai

New member
Local time
Today, 15:13
Joined
Jan 30, 2020
Messages
6
The calculated field in a query would be

Code:
format([Weight] * [Cost], "\R#.#")
The calculated field in a query would be

Code:
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

I’m here to help
Staff member
Local time
Today, 06:13
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Sep 12, 2006
Messages
15,614
@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

I’m here to help
Staff member
Local time
Today, 06:13
Joined
Oct 29, 2018
Messages
21,358
Hi Dave. If it was a "calculated" field, then the question becomes, "what was the 'result type'?"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:13
Joined
Feb 28, 2001
Messages
27,001
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

New member
Local time
Today, 15:13
Joined
Jan 30, 2020
Messages
6
Thank you all for your input.
I changed the Field size to double with format #.# and it worked.
Regards
AB
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:13
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom