Percentage with Conditional Formatting

[access2010]

Could we please receive a suggestion on how to have the value in the field Percentage to have;

On the Form Investments_StockBrowse_F
Green Background if the percentage difference is positive?
Red Background if the percentage difference is negative?

On the Report Investments_StockBrowse_R
No Background if the percentage difference is positive?
Yellow Background if the percentage difference is negative?

Your assistance with this Access 2003 Database will be appreciated.
Thank you
Nicole

 

[bob fitz]

Not sure that I understand the requirement correctly.
Is the form in the attached db what you require.

 

[access2010]

Not sure that I understand the requirement correctly.
Is the form in the attached db what you require.

P.E.R.F.E.C.T.
Thank you Bob Fitz, your firm was what my boss wanted.
I also copied your code from the form to the report and this also works.

We appreciate your assistance, Thank You.
Crystal

 

[bob fitz]

P.E.R.F.E.C.T.
Thank you Bob Fitz, your firm was what my boss wanted.
I also copied your code from the form to the report and this also works.

We appreciate your assistance, Thank You.
Crystal

Always glad to help if I can

 

[access2010]

Hello, Bob.

Could you please suggest two queries that we could use?
One Query to group all the Percentage positive records on a form and in a report.
Another Query to group all the Percentage Negative records on a form and in a report.

If there is no percentage value, those records will not be shown.

Thank you.
Crystal

 

[bob fitz]

Hello, Bob.

Could you please suggest two queries that we could use?
One Query to group all the Percentage positive records on a form and in a report.
Another Query to group all the Percentage Negative records on a form and in a report.

If there is no percentage value, those records will not be shown.

Thank you.
Crystal

Have you tried 《 0 in the criteria row of the % column for the negative records and 》0 for the positive records?

 

[access2010]

Thank you for your suggestion and I have tried;

Control Source = =([current_price]-[TotalCost_$])/[TotalCost_$]<0
and
=([current_price]-[TotalCost_$])/[TotalCost_$]>0

Both choices are unsuccessful.

Could you please offer me an other suggestion, as we would like to print a report and review the form every morning for any equities that have dropped or risen in value.

We do appreciate any suggestion which you can offer.

Nicole

 

[bob fitz]

Nicole
I have to confess that I am a little confused
In your earlier post, you requested two querries but in your last post, you are showing that you are trying to use formulas in a control's Control Source property.
Perhaps you could post a copy of your db with a clearer explanation of your requirements.

 

[bob fitz]

Nicole
I've had another look at the db that I posted back for Crystal and now I think I understand the requirement. Take a look at the db attached.
I have created two new forms which are copies of the original.
In both of them, I have moved the calculated field expression to the forms Record Source property which is using a query statement rather than a saved query. I have called the new calculated field PC. The percentage control on the form is now bound to the PC field by setting the control's Control Source property to PC. I have applied the appropriate <0 and >0 criteria to each of the forms' query statements.

IMHO a neater solution would be to have just one form with a control in the forms Header section which would allow the user to apply a filter to the form. Post back if you would like some help to implement such a solution.

Alternatively, you could use one of the new forms without any criteria and teach users to apply a filter to the form by setting the focus to the percentage control then right-clicking with the mouse and applying the required filter (<0 or >0) themselves.

 

[access2010]

PERFECT. thank you, Bob

What you have done is EXECTLY what my boss wants.
How would I add three Buttons to our form Investments_StockBrowse_F
so that the;
click of one of the buttons LARGER, would filter the group of only the Larger Percentage equities
click of one of the buttons LESS, would filter the group only the Equities that are below cost
click an other button and the filter would be removed

This way we would have one form to use instead of three.

Your suggestions are appreciated as Crystal and myself are volunteers and hopefully will learn more access programing.

Nicole

 

[bob fitz]

Nicole
IMHO it might be better to use an Option Group control rather than three buttons. The use of the Option Group would indicate clearly the filtered state of the form records. See attached db for example.

 

[access2010]

Thank you Bob.
What you have created is the P.E.R.F.E.C.T. form which my boss was looking for.
How can I use the same sorting to print the report Investments_StockBrowse_R?

Crystal and Nicole

 

[bob fitz]

Thank you Bob.
What you have created is the P.E.R.F.E.C.T. form which my boss was looking for.
How can I use the same sorting to print the report Investments_StockBrowse_R?

Crystal and Nicole

Well this proposed solution is a real guess in the dark because:
1) You don't specify how or from where the report is to be opened.
2) Whilst the data in the form and the report have some similarities, the data is different.

In the report, I have created a calculated control, similar to that which I created in the form. However, because every record has a value of 0 for the field Net_Share_Cost, the percentage control also shows 0.

I have entered code in the OnLoad Event of the report which opens a modal/popup form for the user to choose which criteria to apply.
This will work wherever the report is opened from. I have done it this way so that if/when the navigation pane is hidden from the users, the criteria form still opens before the actual report opens. It is considered to be better NOT to give users access to the Navigation Pane and all of its contents.

I have also created another Option Group control and button on the form which can be used to open the report. If the report is opened from this form then the criteria used in the form will be applied to the report (the criteria form will not open).

Do take a careful look at the attached db to try and understand how and why everything has been done.

If you have any questions, please post back.