Unanswered: Create report for Available funds. Change expense amt subtracted based on a checkbox
I have created a database that keeps a realtime record of available funds. In my "ExpenseDetails" table I have a field for "ProjectedCost" which is used when an item is submitted for purchase. After the item has been purchased there is another field for "Actual Cost" where the actual dollar amount is entered. I also have a yes/no (Checkbox) field that is left unchecked until the actual cost has been entered.
I need my report of available funds to use the dollar amount present in the "ProjectedCost" field when the checkbox is unchecked (No) and the "Actual Cost" field when the checkbox is checked (Yes).
In my report the formula for my Available Funds is written as:
I think you're saying you want "Spent" when available, otherwise "Projected"? For starters, it doesn't seem like the yes/no field serves any purpose. The presence of a value in the "spent" field gives you the same information. Thus, depending on your structure (if the field could contain zero...):
Oh, I decided to go ahead and leave the checkbox field in the table. It will serve to show users that the record has been updated and verified with information from our accounting company. My idea is to make a condition so that whenever the AwardAmountSpent field is Null the [YES NO] value will be 0 and when not=Null will be -1. That will allow me to create a query that will bring up all of the records that need updated once an actual cost is available for the AwardAmountSpent field. I guess I still don't need it to create the query, oh well.
It would be great if other users on this site could use this thread!