Fields that change format when moved to a different environment

Monday 21 September 2020 @ 8:52 pm

I recently had complaint from a customer that a numeric field was changing format when moved from one PC to another. The format was fine when run on the developers PC but on the user’s PCs the format would change. I have seen this happen with both numeric and date fields.

The source of the problem is the format option at the top of the list called “System Default”.  If you leave a field formatted with this choice the report will use whatever format is set in the regional setting of the PC where the report is being run. Of course this means that a report might look different when run on another PC. If you don’t want the format to change from one PC to another then you should identify the specific format you want for numeric, currency, date and datetime fields.

Using a specific format should solve this 99% of the time. I have only seen a few instances where a report moved to another environment would have formatting changes despite the format being specified in the report. In those cases the final workaround is to write a formula to display the value. You can use Totext() to convert the field into text that is formatted the way you want. You use the text version for display purposes while using the original field for other functions like sorting and totaling.

Here are some examples:
Totext ({Date.Field}, 'yyyy-MM-dd') //converts a date into a string like 2020-09-21
Totext ({Number.field}, 0,"") // converts a number into a string with no decimals or commas

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server