Calcultated fields in query (how to output in currency format) ?

users
#1

Hello I'm having some problems creating a query in DesigView mode. I'm trying
to calculate a field with an Alias.

So I have a field in which I have MonthlyGrossWage, that field is numeric,
has no decimals (for simplicity), only 5 digits, and its format is currency
$

In my table, I have a the name of my employees, name, lastname, etc... and
also this field MonthlyGrossWage... so I enter John.... Doe... 1150$...

Okay that is fine.

I have my table with all my employees names, lastnames... and also the
salary they get monthly at monthlygrosswage Okay...

How much cost me the employee per hour?

Assuming we have a labour day of 8 hours, I'd divide the monthlygrosswage by
8

So the calculation it's quite simple. "MonthlyGrossWage" / 8 ... in query
designview I add also an alias, so I can output the result of the
calculation in a field... let's say my Alias is HourlyGrossWage

So... my query design view looks like this:

"MonthlyGrossWage" / 8

HourlyGrossWage

Perfect. What happend now is I display the proper calculation in the field
HourlyGrossWage... but! I don't see the result in dollars !!! and of course
I'd like to see it in dollars... so I get the calculation but I cannot setup
the format of the output field...

How may I do this? Cheers

#2

Of course you don't see the calculation result in dollars because it is
just an abstract figure calculated from abstract figures that are stored
in your database. An input form can display formatted numbers by means
of formatted controls. A report, a serial letter or a Calc document
filled with database data can display your database data any way you want.

#3

Yes, I suspect this is impossible, we cannot have a "currency" variable,
because we would have to have thousands of different variables according the
thousands of currencies in the world. What BASE can do is to format the way
the output is presented to the user, however, sadly I've discovered that
even if you change the format of a query to show the results in euros, for
example, sadly, LibreOffice Base don't remember the field format. I've
documented this as a bug:
https://bugs.documentfoundation.org/show_bug.cgi?id=90360
But, yes, since a SQL point of view, it's impossible to show the value with
the currency format. You are right.
Cheers

#4

The grid views of your tables and queries are simple visualisations of
database content and query results. All you can do with this is reading,
editing, sorting and filtering of raw data in most simple data grids.
The user interface consists of office documents carrying forms and
reports. There you can format and layout everything to your liking and
you can export your reports to other file formats.

One thing you may try: Open the table for editing where your query
fetches the currency values from. Near the bottom of the table design
window you find a formatting option. As far as I remember, this is the
place where the default formatting can be saved for a table field and
derived objects such as queries, Writer fields and data source window.

Cheers