Ok, just a bit confused on how to word the calculation in my query so that it reflects on my form. I am using Access 2007! I have a field labeled “UnitCost”; I am attempting to add a calculation that if the Unit Cost exceeds $150.00 (what the business is willing to pay), I want the additional amount displayed in an additional field labeled “AmmountDue” and to appear in red. This will be the amount the employee has to pay out-of-pocket to receive the item. However, if the amount is less than $150.00 the field should reflect $0.00

Any suggestions on how this would work? The next question I will have is since the query is embedded in the form, how do I pull that field so that it is visible on the form itself?

Thanks!

Hi,

(IMG:style_emoticons/default/welcome2UA.gif)

In your query, you could add a calculated column with something like:

AmountDue: IIf([Unit Cost]>150, [Unit Cost]-150, 0)

Then, just add that column in your form to show it.

To change the color to "red," you could try using Conditional Formatting (or just format the textbox to red).

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi,

Thanks, that worked and I added a conditional formatting to make it red.

(IMG:style_emoticons/default/smile.gif)

Hi,

(IMG:style_emoticons/default/yw.gif)

Glad we could help. Good luck with your project.

One additional Question...Will this calculation transfer to a Report or will I need to do the same thing that I did with the form (if thats possible) and create a query and re-enter the calculation?

Thanks

If you use the same query (I'm assuming it is a stored/saved query) for your report, then the calculation should also be available to it.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)