I have faced this twice this month with two different customers. In both cases we design the report to use a numeric field and the report runs fine at design time. But when the report is run within the application it generates a formula error saying “a number is required here”. What is happening is that the data type at runtime is changing so that fields that were numeric at design time are seen as strings at runtime. The source of the problem for one customer was an Excel spreadsheet data source. Excel is notoriously flaky when it comes to data types. The other customer had XML data where the XSD was missing at runtime so all fields became strings at runtime. (See this article for more on XML and XSD Files).
In both of these cases the customer didn’t have the flexibility to change what was happening at runtime, so we needed a workaround. Fortunately there is a trick I recently learned that allows a formula work with a field whether it is a numeric string or a true numeric value. The trick is to nest the field within the following functions:
val ( replace (Totext ( {field} ) , ',' , '' ))
This works because the ToText() function doesn’t error if the field inside the parens is already a text field. So say you have a zip code field that has the value 20176. If the field is a numeric the Totext() turns it into text and the Val() changes it back to numeric. If the field is a string then the Totext() keeps it a string and then the Val() changes it to the same numeric. In both cases the Replace() will deal with any commas that are in the data or that are generated by the ToText () function. Bottom line – this formula will behave the same regardless of which data type the runtime environment decides to throw at the report. One note, to work with currency fields you have to use a second Replace() to eliminate the dollar sign as well as any commas.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)