Page 1 of 1
Integers and decimals together
Posted: Sun Apr 28, 2019 4:30 am
by capemayal1
Some numbers in a field could be a integer or a decimal (not more than 2).
I have set the table field to number with 2 decimals. That works fine.
When I use a report or form, I want the integer to not show the 2 places, but, the decimal numbers to be as decimals,
Example:
7.5 should display as 7.5
7.55 should display as 7.55
100 should display as 100 Not 100.00
150.55 should display as 150.55
Thanks in advance
Al
Re: integers and decimals together
Posted: Sun Apr 28, 2019 9:18 am
by F3K Total
Re: integers and decimals together
Posted: Sun Apr 28, 2019 12:09 pm
by Villeroy
If we are talking about stand-alone form control:
While in design mode, push the 5th button on toolbar "form design" to open the form navigator.
Right-click>Properties... The window caption of the properties dialog indicates the type of control, for instance "Properties: Numeric field"
Unless the control is a "formatted field", right-click>Replace With>Formatted field
A formatted field provides the same number formats as a spreadsheet cell or a text table cell.
If we are talking about form controls that represent a field bundled in a table control:
Column header>RIght-click>Column... opens the properties of the control representing the column.
Column header>RIght-click>Replace With>Formatted field.
If we are talking about an old style report created with AOO without report builder extension:
Open the report for editing and format the respective table cell in the second row. The second row is a place holder for all the rows to be filled with query data.
If we are talking about a new style report created with AOO report builder extension or with LibreOffice:
This report consists of formatted fields, even for text fields.
Right-click>Properties...
Re: integers and decimals together
Posted: Sun Apr 28, 2019 3:35 pm
by Sliderule
You may use the HSQL function
TRUNCATE to meet your needs. As an FYI ( For Your Information ), this function works both with
HSQL Version 1.8 and
HSQL Version 2.?
HSQL Documentation wrote:
TRUNCATE
TRUNCATE ( <num value expr> [, <int value expr>] )
Returns a value in the same type as <num value expr> but may reduce the scale of DECIMAL and NUMERIC
values. The value is rounded by replacing digits with zeros from <int value expr> places right of the decimal
point to the end. If <int value expr> is negative, ABS( <int value expr> ) digits to left of the decimal
point and all digits to the right of the decimal points are replaced with zeros. Results of calling TRUNCATE with
12345.6789 with (-2, 0, 2, 4) are (12300, 12345, 12345.67, 12345.6789). The function does not change the number if
the second argument is larger than or equal to the scale of the first argument.
If the second argument is not a constant (when it is a parameter or column reference) then the type of the return value
is always the same as the type of the first argument. In this case, the discarded digits are replaced with zeros. (JDBC)
So, for example, run the following Query to see how it works:
Code: Select all
Select
INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME,
TRUNCATE(123.4567, 2) as "TRUCNCATE_123.4567",
TRUNCATE(123.45, 2) as "TRUNCATE_123.45",
TRUNCATE(123.4, 2) as "TRUNCATE_123.4",
TRUNCATE(123, 2) as "TRUNCATE_123",
TRUNCATE(.456, 2) as "TRUNCATE_.456",
TRUNCATE(0, 2) as "TRUNCATE_0"
From INFORMATION_SCHEMA.SYSTEM_TABLES
Where INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_SCHEM = CURRENT_SCHEMA
Order By UPPER(INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME) ASC
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: integers and decimals together
Posted: Sun Apr 28, 2019 3:53 pm
by capemayal1
Thank you Villeroy.
I am using LO 6.0.
In this one control, one of many similar, the table design is set as numeric with 2 decimal points, 0 default - displays as 0.00, and format as 1234.56.
If I do not format as I have, then the format is currency, even if I set the type as numeric.
This then follows through to the queries, forms, and reports.
As you said, the controls are already formatted controls. Some of the values are whole numbers and some are decimals. I want them to be displayed, stored, and calculated as entered - either a whole number or a decimal.
Perhaps the term I'm looking for is to enter a free-form number, e.g. 100, 7.5, etc With a default as 0 for calculation purposes if no entry should be needed - an empty control so to speak.
Re: integers and decimals together
Posted: Sun Apr 28, 2019 4:34 pm
by capemayal1
Would it be possible, or better, to use a CASE WHEN function -
case when the numbers, assuming the number is 1, to the right of the decimal point are .00 then 1 else 1.00
Re: integers and decimals together
Posted: Sun Apr 28, 2019 7:37 pm
by Villeroy
capemayal1 wrote:In this one control, one of many similar, the table design is set as numeric with 2 decimal points, 0 default - displays as 0.00, and format as 1234.56.
Simply change the number format of the formatted fields as advised by "F3K Total". In the form navigator you can easily select all formatted fields with Ctrl+Click, then click the elipsis button near the number format and you will get the exact same number format dialog as in Calc where you can enter the number format code 0.## which means: one leading zero and a decimal point with up to 2 decimal digits when needed. The language at the top right corner of the dialog should allow for decimal comma. Any English setting will do.