Currency Format Doesn't Display in Report 1

[mcongdon]

I have a simple report based off of a query. All 7 fields are set to be formatted as currency when displayed on the report. When opened in print preview view, 5 of the text boxes show up as number fields displaying as many decimal points as were calculated. When I go back and check how they are formatted, they are still formatted as currency. I have tried deleting the text box and putting a new one in, but the same thing happens. I have tried formatting the underlying tables and queries as currency as well. Nothing has worked. Any ideas on how to make these fields display as currency would be much appreciated.

Just to clarify, these fields are not calculated on the report, but they are sums in the underlying query. The values they are summing are formatted as currency as well.

Thanks!

 

[dhookom]

I never waste time formatting fields in tables or queries. I apply all formatting in the controls (text boxes) on forms and reports.

Duane
Hook'D on Access
MS Access MVP

 

[JBinQLD]

Mate, I know it sounds obvious, but the Text Box property underneath Format, where you've chosen Currency, is "Decimal Places". You've tried setting that to two right?

 

[mcongdon]

I usually don't format tables or queries either, but when it wasn't working right I gave it a shot. I have tried setting the decimal places to any number, and none of them make the controls display in the fashion that what they are supposed to.

 

[JBinQLD]

If you mess wih your data to force a calculation to result in X.000000 does the report show needless 0's or just the true integer result?

Try trouble shooting.

Make a completely blank report and add just the troublesome field. Does is still happen?

If so, make a dead simple table with one field an type in 1.0,1.23, 1.234, and display those in your report. Does that work? Narrow down whether it's the table, query or report misbehaving cos it sounds like a weirdo one

 

[mcongdon]

Okay, I ran the query in datasheet view and it displays just 2 decimal places. When it gets put on a report, it displays the true integer result, regardless of what amount of decimal places I tell it to display. I tried the dummy table with random numbers with different amounts of decimals, and when I display it on a report formatted as currency, it displays properly.

 

[mcongdon]

I just noticed that the field at the bottom that sums the different fields that arent displaying properly is reading:

78629.42104920.29130590.551074

This is supposed to be the sum of

187,155.8
78,629.42
104,920.29
130,590.55
107,437.98
143,159.8
146,965.33
14,883.2543

The sum should be
913742.4243
showing as
$913,742.42

These are all the numbers that I want to display as currency, but they are displayed as shown above.

 

[mcongdon]

Sorry, they are displaying without the commas as well. Just exactly what they are calculated as.

 

[JBinQLD]

what is the SQL of your report record source? And which field is being a bugger?

 

[dhookom]

Do the field values display left or right aligned in the datasheet view? If they are left aligned, they are being treated as text strings rather than numeric.

Duane
Hook'D on Access
MS Access MVP

 

[JBinQLD]

Ah! At least that confirms it's treating them as text.

12+14 = 1214 in text world and that's clearly the kinda thing that's going on in your report.

If you create a new report, no grouping, sums, or anything and add the troublesome on, is that ok? Are you displaying the value of a running sum or using a sum functions at the bottom?

 

[JBinQLD]

The sum at the bottom is definately the real clue, it's a centered concatenation of your different fields. The control source must be field1+Field2 etc. If you make the control wider it will read

187,155.878,629.42104,920.29130,590.55107,437.98143,159.8146,965.3314,883.2543

To fix this, change the control source to CCur(Field1)+CCur(Field2)+etc and that should work. If it doesn't perhaps try CCur(Field1)+CCur(Field3).

The control that aren't displaying, do the same to them - set control sources to =CCur(Fieldname)

Let me know quick, i need to go zzz soon!

 

[mcongdon]

When I change the sum to ccur(field1)+ccur(field2).... it returns #error. When I change each field's control source to =ccur(fieldname), it always returns $0.00. Getting close!

 

[JBinQLD]

Hmmm. The fields obviously contain values that access cant render to a currency value. Can you pls do as requested earlier and post the SQL of the reports record source and let me know which field is causing a prob?

 

[mcongdon]

Okay, this probably needs some explaining. The fields are being pulled from a query that combines the results of a bunch of other queries so that they can all be used in one subreport. The query that combines the others:

SELECT que_JanConcessionMOR.JanConcession, que_FebConcessionMOR.FebConcession, que_MarConcessionMOR.MarConcession, que_AprilConcessionMOR.AprConcession, que_MayConcessionMOR.MayConcession, que_JunConcessionMOR.JuneConcession, que_JulConcessionMOr.JULConcession, que_AUGConcessionMOR.AUGConcession, que_SepConcessionMOR.SepConcession, que_OctConcessionMOR_1.OctConcession, que_NOVConcessionMOR.NOVConcession, que_DECConcessionMOR.DECConcession, que_MonthlyGoalsExtract.January, que_MonthlyGoalsExtract.February, que_MonthlyGoalsExtract.March, que_MonthlyGoalsExtract.April, que_MonthlyGoalsExtract.May, que_MonthlyGoalsExtract.June, que_MonthlyGoalsExtract.July, que_MonthlyGoalsExtract.August, que_MonthlyGoalsExtract.September, que_MonthlyGoalsExtract.October, que_MonthlyGoalsExtract.November, que_MonthlyGoalsExtract.December
FROM que_JanConcessionMOR, que_FebConcessionMOR, que_MarConcessionMOR, que_AprilConcessionMOR, que_MayConcessionMOR, que_JunConcessionMOR, que_JulConcessionMOr, que_AUGConcessionMOR, que_SepConcessionMOR, que_OctConcessionMOR, que_NOVConcessionMOR, que_DECConcessionMOR, que_MonthlyGoalsExtract;

The ones with the goals are fine, they display properly. The ones with concessionMOR in them do not. They are pretty much identical except for the one month criteria. Here is an example:

SELECT nz(Sum((tbl_BLOTTER![Execution Price]-tbl_BLOTTER![Street Price])*tbl_BLOTTER!Quantity)+Sum(tbl_BLOTTER!Commission)) AS JanConcession
FROM tbl_BLOTTER
WHERE (((Month([tbl_BLOTTER]![Trade Date]))=1) AND ((Year([tbl_BLOTTER]![Trade Date]))=[Forms]![frm_MonthBlotSelect]![cboYear]));

These concesstion fields are the ones that don't display properly.
Thanks for all the help!

 

[JBinQLD]

Feeling tired but try amending to this:

SELECT [red]ccur([/red]nz(Sum((tbl_BLOTTER![Execution Price]-tbl_BLOTTER![Street Price])*tbl_BLOTTER!Quantity)+Sum(tbl_BLOTTER!Commission))[red])[/red] AS JanConcession
FROM tbl_BLOTTER
WHERE (((Month([tbl_BLOTTER]![Trade Date]))=1) AND ((Year([tbl_BLOTTER]![Trade Date]))=[Forms]![frm_MonthBlotSelect]![cboYear]));

but you're gonna have to do it on every one unless you make a cut down version to test.

 

[mcongdon]

THANK YOU!!!! It worked wonderfully!

 

[JBinQLD]

No drama mate.

JB