Number Format Issue

[Tophan]

I have a control on my report that will not accept the number formatting.

I need it to be displayed as currency but it is showing as a general number on the report e.g. $1,451.10 is displaying as 1451.1

This control source is from a query with the following formula

SubT: DSum("[Total]","qryPaymentsToDate2","[OrderNo]='" & [OrderNo] & "' And [tblInvoices].[InvoiceNo]<=" & [tblInvoices].[InvoiceNo])

I've tried changing the formatting in both the query and the report by selecting the currency option or by typing $#,##0.00 but the results are the same in both.

Any suggestions?

 

[CJ_London]

if the formatting is not working as you suggest in the report, it implies dsum has not returned a number, but text, which is highly unlikely. Formatting in a query is irrelevant - format properties are not passed on to a form or report control except at the time of the controls creation.

Suggest review what you are doing and where you are doing it

 

[plog]

[OrderNo]='" & [OrderNo] & "' And [tblInvoices].[InvoiceNo]<=" & [tblInvoices].[InvoiceNo]

Your criteria argument is illogical. You can't reference a field in a table other than the one you are DSumming. DSUM has no idea what [tblInvoices].[InvoiceNo] is. You need to rethink your criteria.

 

[Tophan]

Thanks...I changed the formula to

SubT: Format(DSum("[Total]","qryPaymentsToDate2","[OrderNo]='" & [OrderNo] & "' And [tblInvoices].[InvoiceNo]<=" & [tblInvoices].[InvoiceNo]),"Currency")
and it's working...in both query and report

 

[CJ_London]

only problem with the format function is you have converted it to a text value - so you cannot add/divide etc. If you don't need to do this then not a problem