Creating Calculation Field in Report

[PandoraGirl]

I have a form that collects data about maintenance agreements including term dates (start/end) and amount. I have a hidden field that calculates the number of months of the term. I want to create a query or report that has the named months (January 2022, etc.) in the upcoming budgeting cycle as calculated fields. I want the calculated field to look at the month (January 2022) and if the term start date is in January 2022 calculate the following: If the number of months is equal to, or less than, 12 AND the amount, divided by the months, is greater than, or equal to 10,000, then put the amount in the start month and populate the months to end month. If the number of months is greater than 12 and the amount, divided by the months, is greater than, or equal to 5,000, then put the amount in the start month and populate the months to end month. If neither of these scenarios is true, populate the start month with the total amount. I tried creating the following formula as a starting place

= iif(eval(Forms![Maintenance Agreements]![Months] =<12 and Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months] =>10000, Forms![Maintenance Agreements]![Account] / Forms![Maintenance Agreements]![Months] ,""))

but keep getting an "The expression you entered contains invalid syntax" error. Since I'm asking for help, I figured I would try to get help with the end result as I am new to Access. Thank you in advance.

[CJ_London]

looks like you have a closing parentheses in the wrong place, there are two at the end, either move one to just after >=10000 to complete the eval function or move the eval( to before the iif. Which depends on whether you ran to evaluate the 'if part or the result of the iif

[ssanfu]

I suggest you DO NOT use spaces in object names. A better form name would be "MaintenanceAgreements" (no space)

---------

You have

= iif(eval(Forms![Maintenance Agreements]![Months] =<12 and Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months] =>10000,
 Forms![Maintenance Agreements]![Account] / Forms![Maintenance Agreements]![Months] ,""))

Shouldn't [Account] be [Amount]??

---------

You have listed 3 conditions:
1) the number of months is equal to, or less than, 12 AND the amount, divided by the months, is greater than, or equal to 10,000
then put the amount in the start month and populate the months to end month

2) If the number of months is greater than 12 and the amount, divided by the months, is greater than, or equal to 5,000
then put the amount in the start month and populate the months to end month

3) If neither of these scenarios is true, populate the start month with the total amount


For 1 & 2, is "the amount" the value of Forms![Maintenance Agreements]![Amount]??
Or is it the value of Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months]??

For 3, I'm guessing you want the value from Forms![Maintenance Agreements]![Amount]? (total amount?)

---------

This is the formula I came up with: (* untested **)

= IIF(Forms![Maintenance Agreements]![Months] =<12 and Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months] =>10000,
 Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months],
 IIF(Forms![Maintenance Agreements]![Months] >12 and Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months] =>5000,
 Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months] , Forms![Maintenance Agreements]![Amount]))

---------

You also stated: "I want the calculated field to look at the month (January 2022) and if the term start date is in January 2022".
At this point, I'm not sure where the the term start date fits in this calculation.

What do you want to do if the the term start date is February 2022?

---------

I WOULD NOT use calculated FIELDS in a table. I would do the calculations in a query.



BTW, welcome to the forum....

[June7]

Why are you using Eval() function? Eval() will take a string of characters like: "5 x 3" and convert that to an arithmetic expression to return a result of 15. Or some other string within quote marks https://docs.microsoft.com/en-us/off...plication.eval.

I don't see need for it in the posted expression.

[PandoraGirl]

Thank you all for reaching out. I am attaching my sample database (backup copy to make it small enough) and a snapshot of what I would like my report or query to look like with the amounts in the Month columns being calculated fields. The months would continue to the end of 2025.

June7 - I was using Eval because that is what I found online. Thank you for explaining what it is used for.
Steve - I tried your formula but I received the same error. Regarding your questions... "For 1 & 2, is "the amount" the value of Forms![Maintenance Agreements]![Amount]??
Or is it the value of Forms![Maintenance Agreements]![Amount] / Forms![Maintenance Agreements]![Months]??" It would be Forms![MaintenanceAgreements]![Amount] / Forms![MaintenanceAgreements]![Months] "For 3, I'm guessing you want the value from Forms![Maintenance Agreements]![Amount]? (total amount?) Yes What do you want to do if the the term start date is February 2022?" In my Excel file I use the following formula "=IFERROR(IF(K$3=MEDIAN(EOMONTH($F7,-1)+1,$G7,K$3)" to identify the month. If the month in K and F match, an amount is entered, if not, a zero is inserted in the cell. I have updated my database so I don't have spaces in my Field Names.

Thank you all again for your help and the warm welcome!

[ssanfu]

I have modified your dB.


One of the errors in the IIF formula is because

= iif(eval(Forms![Maintenance Agreements]![Months] =< 12 and Forms....

You have the operators in the wrong order. It must be (equals sign last)

= iif(eval(Forms![Maintenance Agreements]![Months] <= 12 and Forms....

Another thing is that you have designed your dB like a spreadsheet. This is so common that there is a name for it - it is termed "committing spreadsheet".
I modified your table designs, added a firm and added 2 queries. And I renamed things.
The calculation for the Months and the "Amount per Month" is done in the query.
I did not look at/change the report.

The controls on the forms that have a yellow background are hidden (my way to know what is hidden)


This is not complete, but maybe it will help you move forward.......

[PandoraGirl]

Steve,

Thank you so much for your help! I learned a lot just looking at what you have done. I noticed that you added a separate Primary Key field to my Account table instead of using the account number. Since each account number is unique, I thought it would be ok to use. Can you tell me why you don't think that is a good idea? As for my "committing spreadsheet", lol...We have been using Excel for our budget templates, which takes a lot of prep and rework (the managers tend to type over our formulas, etc.). I am trying to see if I can utilize Access going forward. I can see how the data can be entered into Access but we will need to export it into a format that we can use to upload into our financial system. I have attached a sample file that has a Maintenance Agreements tab, so you can see how we have been doing it (this is one tab of a multiple tab file), and an Upload tab, which shows the format of the data we will need to export. I thought a query or report would be the best option but not sure how to do that without "committing spreadsheet". Thanks for all of your help so far!

[ssanfu]

<snip> I noticed that you added a separate Primary Key field to my Account table instead of using the account number. Since each account number is unique, I thought it would be ok to use. Can you tell me why you don't think that is a good idea?

You had the Primary key (PK) field as a test type field. I only use Autonumber type fields for the PK field.
Here are two sites about Primary keys - one discusses the different ways to determine PK field(s) and the 2nd is what I go by.

1st)
Relational Database Primary Keys

2nd)
Microsoft Access Tables: Primary Key Tips and Techniques