Converting text to display as currency in Report

[chengsi]

I have based a report on a crosstab query. This query has eliminated all formats, i.e. all fields including those containing numbers are now stored as text.

Is there a way that i can get Access to reformat these 'text' fields as currency or standard 1,234,567 notation on the final report?

Thanks.

[NeoPa]

Either code in the TextBoxes of the Report as

=Format(Val([FieldName]),"#,##0.00")

or, possibly neater, create a query using the results of the CrossTab Query and create fields in the form

Val(qryCrossTab.FieldName) AS FieldName

The results of such a query would be able to be used in the normal way in your report.

[pks00]

Try formatting the field using Currency
e.g.

Format(myfield, "Currency")

[chengsi]

Either code in the TextBoxes of the Report as

=Format(Val([FieldName]),"#,##0.00")

or, possibly neater, create a query using the results of the CrossTab Query and create fields in the form

Val(qryCrossTab.FieldName) AS FieldName

The results of such a query would be able to be used in the normal way in your report.

Hi NeoPa,

If I was using your first suggestion (i.e. code in the TextBoxes of the Report as
[Code]=Format(Val([FieldName]),"#,##0.00") , where/how do i input this code?

Thanks.

[NeoPa]

When you're designing your report, there are TextBoxes that display all the data - in the detail section.
Copy and Paste this (including the '=') into the 'Control Source' property (of the TextBox you want to display the value).