Decimals Gone Wild! (1 Viewer)

[brucesilvers]

I have a numeric field on my report that I want displayed with 2 decimal places, regardless of whether the 2nd decimal place is a zero (i.e., if the number is 7.2, I want 7.20 displayed).

The format for the field is currently Fixed / Decimal Places: 2.

The display seems to be ignoring the fixed/2-decimal rule. If the number is 7.2, it is displayed as 7.2 instead of 7.20.

The table source for that field is a currency field. The query does not format the field.

I thought perhaps that Access was seeing the field as a text string, so I tried using: Val([myfield]) instead of simply [myfield], but that only resulted in "#Error", which errored out the calculated fields that follow.

Other columns in the report that originate with the same table source (only difference is the date parameters - it's a PrevYear vs. CurrYear thing) are correctly displaying the 2 decimals.

I'm currently stumped, having searched the help files and exhausted apparent logic. Any ideas? Thanks!

 

[andrew93]

Try using a format (in your field on the report) of

0.00; -0.00

If that doesn't work, try setting the format in the query.

HTH, Andrew

 

[brucesilvers]

Still not working

I tried setting the format as you described, first in just the report and then just in the query, but the results are the same.

The original field in the underlying table (Invoice Lines) is:

FieldName: Sales
Data Type: Currency
Format:[none-not specified]
Decimal Places: Auto

The data is fed through a query (Master Sales Query) which simply joins the Invoice Lines table to the Additional Lines Info table and the Customers table, and that query performs NO calculations (simply pulls the data as it appears in the underlying tables).

The Salesrep Sales Analysis - Reps query performs the following calculation to create the CurrApr field:

CurrApr: Sum(IIf(Month(Now())=1 And Year([Master Sales Query]![Invoice Date])=Year(Now())-1 And Month([Master Sales Query]![Invoice Date])=4,[Master Sales Query]![Sales],IIf(Year([Master Sales Query]![Invoice Date])=Year(Now()) And Month([Master Sales Query]![Invoice Date])=4,[Master Sales Query]![Sales],0)))
Format:[none-not specified]

The final query that feeds the actual report checks for 0 values and displays them as nulls:

CurrApr: IIf([Salesrep Sales Analysis - Reps]![CurrApr]=0,"",[Salesrep Sales Analysis - Reps]![CurrApr])

The report simply displays the field unformatted.

In both the query and the report, the field value seems to be displayed as a general number (no commas, any number of decimal places).

I have tried using the following formats, in just the query and then in just the report, all of which produce the same results:
· Currency
· Fixed, decimals 2
· General, decimals 2
· 0.00;-0.00
· #.##;-#.##

I'm quite stumped by this problem. Do I have to use some kind of string replace statement? If so, will I then run into problems since the report performs calculations based on the value of that field?

Normally I wouldn't worry about this, but the project requires that I exactly duplicate an existing report coming out of the old unix-based system, which displays the values with 2 decimal places, regardless of whether the last decimal place is 0.

 

[gblack]

You could...

Try putting this into an unbound text box:

= IIF(right(cstr([CurrApr]),3)=".", [CurrApr], [CurrApr] & "0")

This should change your "CurrApr" field to a character value...then add a 0 to the end, if the 3rd position from the right is not a decimal place.

HTH,
Gary

 

[brucesilvers]

Tried the cstr() function and it errored out the field as well as the other, calculated fields based on it.

I also tried Round([CurrApr],2), but it's still displaying only a single decimal place (1.5) instead of 2 (1.50).

 

[gblack]

Ok

where did you try the cstr()?

Did create an unbound text box and put

= cstr([fieldname])

??? Then run the report?

If you simply delete a bound text box ad try to use functions, access will give you an error message.

 

[gblack]

Anther thing you can do is....

make a new field in your query that changes the value to a string (i.e. via the cstr() function).

Then when you pull it up in your report as a bound text field all the work will be done.

 

[brucesilvers]

It's a string!

OK, I finally understand what's happening here. The following formula has transformed my currency field into a text string because of the return Null statement:

CurrApr: IIf([Salesrep Sales Analysis - Reps]![CurrApr]=0,"",[Salesrep Sales Analysis - Reps]![CurrApr])

Once that was done, formatting options became limited to string manipulation, which I really wanted to avoid since I have follow-on fields in the report that perform calculations.

I changed the Null to 0 (zero), which eliminated the decimal problem in the queries and report, set the format of the report field to Fixed, 2 decimals, then added conditional formatting so that if the field is 0, the font color changes to white, which effectively makes it invisible on the printout. The calculated, follow-on fields are treated the same way, so if the original field is 0, those fields equal 0 and get the same conditional (white) text formatting.

Thanks gblack for your help on this. You got me thinking in the right direction and now I can keep at least some of my hair ;-)