Formatting Unbound Control

[SltPhx]

Hello All,
I am new to access and have been sitting with an issue since 3-4 days. Do let me know if my concept is wrong or if there's a better way to tackle this issue...



I have a form that displays calculations of PASS and FAIL. So the PASS control button is unbound and is displayed as 98% if it is a whole number and if it is a decimal it dispays as 84.3333333333333 (13 decimal places and no percent symbol) whereas FAIL(bound) has the control source of 1-PASS, Format-> Percent and Decimal Place -> 2. Values for FAIL are displayed accurately as I want it.. for eg: 2.00%, 15.67% etc.

Calculations for PASS is done in the 'on click' event of a 'Show Statistics' command button. These results are not stored in any table.

I hope my question is clear.. Please let me know what's wrong .. and how to correct it. Thanks much for your time and help...

Warmest Regards,
SLTPHX

[rpeare]

So you're saying when you open your form the values for FAIL are being stored with extra digits that you do not want to see in your table? or that on your form your PASS number is showing extra decimals you do not want?

If it's the former you can do something about it but there's no real reason to, you can display the percentage however you want in queries/reports.

If it's the latter you have to apply the same format as you did to your bound column.

[SltPhx]

Hello,
Thanks for the quick response.

When I open the form both PASS control and FAIL control are empty.
When I click on 'Show Statistics'.. The PASS control displays the correct value but not format - No percent symbol and upto 13 decimal places.
FAIL control displays the results as I want.. 2 decimal places and % symbol. So no issues with FAIL control.

For the PASS control even if I set the format as percent with 2 decimal places in the property sheet it does not follow it. I want PASS control to display the values in the same format as that of the FAIL control.
I am not storing the values for PASS and FAIL in any table... no queries or reports are linked to this form.
Let me know if you need anymore information.

[June7]

Show the code that calculates the Pass value. It is possible the result is a text value, not numeric, and control format will not apply. Apply Val function on the calc.

Your first post says Fail is bound but then seems to have an expression in the ControlSource so must not be bound.

[SltPhx]

Sorry for the mistake...

This is how the PASS control gets its value using a macro:-

When I click on Show Statistics control button->
On Click event -
SetProperty
Control Name: Pass
Property: Value
Value = DCount(" [Name?] ","LastDateLinked"," [Name?] = 'Yes' OR [Name?] = 'N/A'")/[TotalCount])

(Here I tried multiplying it by 100 to display in the percent format, and set the format of the control as percent with 2 decimal places -> that did not work... So i removed the " * 100" after the expression 'coz it was easier to calculate the FAIL value)


FAIL control gets its value:-

PropertySheet-> Data -> ControlSource -> =1-[Pass]
PropertySheet-> Format -> Percent
PropertySheet-> Format -> Decimal Place -> 2


How do I assign the field type for an unbound control? Sorry.. just started working with Access recently...

[rpeare]

is value the actual name of your field? If so don't use it, reserved words (value, name, date, now, etc) will cause heaps of trouble if you name your objects with them.

To change the format of an unbound form click on the object, open the properties window and look at the FORMAT property, you should be able to set it to decimal there.

[SltPhx]

No, Value is a field that is set when creating a macro. It is not the name of my text box/field...

http://msdn.microsoft.com/en-us/library/ff194340.aspx


And yes, I tried different combinations in property sheet of that control.. Still it displays with 13 decimal places. I tried all the numbers available from 0-10. No matter what I set it as, it displays 13 decimal places.

[rpeare]

can you upload a sample database (strip it down to the bare essentials and put in data that can replicate your problem) in access 2003 or lower format and I can take a look

[SltPhx]

Have no access to 2003 version... I could send you a sample through 2010 version..


I have a form that has a control button "Show Statistics" and 3 textboxes - TotalCount, PASS and FAIL. When I open the form the texboxes are empty.

The 'Show Statistics' command button is set such that 'on click' event it sets the value of
TotalCount = DCount("[Date]","LastDateLinked")
(Where DATE = Field of a query
LASTDATELINKED = Name of the query)
Pass = DCount(" [Name?] ","LastDateLinked"," [Name?] = 'Yes' OR [Name?] = 'N/A'")/[TotalCount])
(Where Name = Field of a query
LASTDATELINKED = Name of the query)
That's all in the macro.

Fail textbox = 1- [Pass]
(This is specified in the ControlSource of the textbox)


Both PASS and FAIL is set to Percent Format and 2 decimal places.

So, as soon as I click on 'Show Statistics'... TotalCount, PASS and FAIL text boxes are populated with values but only for PASS - format is incorrect.
Was that helpful?

[SltPhx]

Never Mind.. I found another method.. so that the decimal points will be truncated.

PASS = Round(DCount(" [Name?] ","LastDateLinked"," [Name?] = 'Yes' OR [Name?] = 'N/A'")/[TotalCount]),2)

This will display the value for PASS with 2 decimal points.

If anybody knows a method to display the % sign along with this, kindly let me know...

Rpeare, thanks much for your suggestions... Thanks for your time...

[June7]

PASS = Round(DCount("[Name?]","LastDateLinked"," [Name?] = 'Yes' OR [Name?] = 'N/A'")/[TotalCount]),2) & "%"

Be aware, Round uses an even/odd rule when number following the place to round is a 5.

Round(74.5,0) will round to 74
Round(46.5,0) will round to 46
Round(47.5,0) will round to 48

Format function does not follow even/odd rule.

You could try the Val function which should return a numeric value that the textbox format setting could handle:
Val(DCount("[Name?]","LastDateLinked"," [Name?] = 'Yes' OR [Name?] = 'N/A'")/[TotalCount]))

[SltPhx]

Thank you June7 for the valuable input..

However, I am using the Round function for 2 decimal places.. and it seems to be working fine. Any number with the 3rd decimal value as 5 or more, rounds up the 2nd decimal value to the next higher digit and if it is lesser than 5, it rounds it to a next lower digit.

I added the "%" sign with Round() and it works fine for the PASS controls.. but the FAIL controls show a 'type#' error. Is it because FAIL = 1-[PASS] and PASS value is taken as '95%' or '95.67%' than just '95' or '95.67'?

Thank you so much for your time...

[June7]

Including the % sign results in a string with a special character. Cannot calculate. If the textbox format settings won't handle the result of the Round function (without the % symbol), try my suggestion with Val function.

Unless Access has changed the Round function in 2010, even/odd rule applies.
Round(37.945,2) = 37.94