Use two decimal places only when required (1 Viewer)

HairyArse

Registered User.
Local time
Today, 16:55
Joined
Mar 31, 2005
Messages
92
Is it possible to modify a control on a form so that it shows to 2 decimal places only when required and show to 0 decimal places the rest of the time.

I'm working on our invoicing system and the field quantity.

99% of the time we are dealing with whole numbers and 2 decimal places is not required. However, every now and then we need to display decimals, i.e. 29.50.

I don't want to force 2 decimal places for all quantities because 10,000,000.00 just looks silly.

The only way I can think to do this is to have 2 copies of the quantity control and show or hide one or the other depending on if we are dealing with a decimal but I'm hoping there's a more elegant solution?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:55
Joined
Feb 19, 2013
Messages
16,553
rather than a second field, use a bit of code in the form current event - something like

Code:
 if mynumberfield>=10000 then
     mynumberfield.format="#,##0.00"
 else
     mynumberfield.format="0"
 end if

Note this would not be suitable in a continuous form since the control needs to be the same format for each instance of the control (same goes for your alternative field suggestion).

Only way you could do it in a continuous form would be to have your source query format the value as text - but then you won't be able to edit it
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:55
Joined
Jan 20, 2009
Messages
12,849
If you don't mind the point always being displayed, this Format will only display the decimal digits if required.

Code:
#,##0.##
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:55
Joined
May 7, 2009
Messages
19,169
or remove any formatting on the table level.
 

HairyArse

Registered User.
Local time
Today, 16:55
Joined
Mar 31, 2005
Messages
92
I am using continuous forms, so do need to format each line individually.

I could format each quantity as a string in the main query, however, I do calculations on the quantity, too, which would mean I'd need to call the quantity twice, once as a double and again as a string, which seems equally as inelegant as just calling the variable twice and showing or hiding the appropriate one.
 

HairyArse

Registered User.
Local time
Today, 16:55
Joined
Mar 31, 2005
Messages
92
or remove any formatting on the table level.

That's not a bad idea, actually. But it's a little tricky given this is a long-established project with many thousands of lines of records I'd have to go back and modify.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:55
Joined
Feb 19, 2013
Messages
16,553
with many thousands of lines of records I'd have to go back and modify.
you'd only have to go into the table design and modify it once
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:55
Joined
Feb 19, 2013
Messages
16,553
I do calculations on the quantity, too, which would mean I'd need to call the quantity twice, once as a double and again as a string
you would modify your calculation to convert a text to a number using the val or cdbl functions

val("1").... 1
cdbl("1").... 1.0

val("125")*5=625
 

Users who are viewing this thread

Top Bottom