In my report I’m using a concatenation like this:
[mem1]&" “&[date1]&” "&[currency1]
where [mem1] is a memo field, [date1] is of type date & time and [currency1] is of the type currency.
As standalone fields, each field could be formatted, is there an formatting option for concatenated fields?
Because after concatenation it looks like this
“Text 1 blablabla Friday, 07. July 2023 09:47:23 29000” and it should look like this:
“Text 1 blablabla 07.07.2023 29.000€”
I don’t understand where the report gets its locale from. The attached database has a report “Formatted Concatenation” which is the result of clueless try-and-error. It works with the global locale set to English and German as well.
The formula is [Category]&": "&TEXT(DAY([Date]);"00")&"."&TEXT(MONTH([Date]);"00")&"."&YEAR([Date])&" "&TEXT([Value];"#,##0.00")
It looks as if the TEXT function fails with date formats. However, it works with decimals and integers. So I concatenated the day.month.year numbers and formatted the decimal with an English number format code. A German one fails. It always shows the German number format for the English format string regardless of the office locale and the report field’s number format locale.
FilterData_FmtConcat.odb (112.6 KB)
1 Like
Great, this did it:
[Mem4]&" “&TEXT([Neue E-Grenze];”#,##0.00")&“€”&" “&[Mem5Art]&” “&TEXT(DAY([F-Datum1]);“00”)&”."&TEXT(MONTH([F-Datum1]);“00”)&"."&YEAR([F-Datum1])
Thank you!
Use the TEXT() function on [date] with dates format, search for ‘Date and Time Formats’, the same that can be used on cell’s format.
thank you mariosv, but in base report this is not working - the format is unchanged
[mem1]&" "&TEXT([currency1]; “##.###”)&… results in:
if I change the field [currency1] into a direct number like 1234567 b. e., than its working, but not with a field content
Sorry, I was thinking in Calc.
Which is quite correct because the formatted fields in forms and reports behave very much like spreadsheet cells.
Are you sure about English “Friday, 07 July”? Switch your office locale to German (Tools>Options>Languages) and see if it makes a difference.
I switched already to local German, the whole truth is much stranger:
“Freitag, 3. März 2023 um 00:00:00 Mitteleuropäische Normalzeit” this is the whole concatenated result of a date field.