Integers and decimals together

Creating tables and queries
Post Reply
capemayal1
Posts: 90
Joined: Fri Dec 31, 2010 5:16 pm

Integers and decimals together

Post 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
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: integers and decimals together

Post by F3K Total »

hello, try format

Code: Select all

0.##
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: integers and decimals together

Post 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...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: integers and decimals together

Post 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.
capemayal1
Posts: 90
Joined: Fri Dec 31, 2010 5:16 pm

Re: integers and decimals together

Post 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.
capemayal1
Posts: 90
Joined: Fri Dec 31, 2010 5:16 pm

Re: integers and decimals together

Post 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: integers and decimals together

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply