Variable Decimal Places in Report

[mrcost]

I am trying to get the access report to change the number of decimal places based on the user input on a form. The only two choices I want are 0 and 2 decimal places in a percentage taken from a query. I have the query linked to the form to change automatically, but can't get the report to change the display automatically. Using Access 2003.

Thanks in advance,

Mrcost

 

[Uncle Gizmo]

If you're talking about information in the "detail section" of the report then I think you are stuck with whatever format the text box is at.

If that's the case then one solution is to build a query that produces a text version of the results you want And feeds those to the report.

So in other words instead of displaying a "Number" you display a text value, you can format it how you like.

 

[mrcost]

Thanks a lot. I researched a bit more based on your recommendation and added two columns to the query based on the calculated field as follows:

%Text0: Format([% Complete],"0%")

%Text2: Format([% Complete],"0.00%")

Then I did an IIf statement to select the right one based on the user input on the form.

Thanks again for the quick response.

-Mrcost

 

[Uncle Gizmo]

Your answer made me realize there is another way that may work.

Where your text box appears in the detail section of the report, you could have two text boxes, one superimposed on the other, one could have one format setting and the other one could have another format setting. You would then make them visible or invisible using the "format event" of the report dependant on some criteria. Both text boxes would have to be linked to the same data source.

 

[Kiwiman]

Howzit

Or you could have just the one and apply a different format depending on your criteria, in the ON Open event of your report.

I use this approach depending on which user wants to see which format.

dim strFormat as string

if userinput = yourcriteria then
strFormat = "0%
else
strFormat = "0.00%
end if

yourtxtbox.format = strFormat