Rookie banging head against wall with iif

[~Rachelle~]

I am a major rookie sorting my way thru here... I would deeply appreciate any assistance I can get on my report.

What I would like to do is add two fields together and then if the result is more than 245, have it return 245. If it is less than 245, have it return the true value.

Here is what I'm trying to put in the footer (and is not working)
=Sum(IIf([Wk 2 Total]+[H2]>245,"245",[Wk 2 Total]+[H2]))

Wk 2 Total + H2 is 255... and it is returning 255 using the above.

Thank you in advance.

 

[Galaxiom]

Sum as a ControlSource can only sum a field that already exists in the recordset.

Derive a field in the RecordSource query using the IIF part. Then Sum that field.

BTW. Don't put quotes around the numbers.

 

[~Rachelle~]

Thank you. Not sure that I get it but I'm trying.

 

[MarlaC]

What Galaxiom is saying is that you should put the calculation into the recordsource for your report. If the report is currently fed directly by a table, change it to a query. In the query, add a field -- let's say Wk2Calc -- like this:

Wk2Calc:Sum(IIf([Wk 2 Total]+[H2]>245,[COLOR="Red"]245[/COLOR],[Wk 2 Total]+[H2]))

Or, if you have a totals query (with GROUP BY, etc.), instead you would put this:

Wk2Calc:IIf([Wk 2 Total]+[H2]>245,[COLOR="Red"]245[/COLOR],[Wk 2 Total]+[H2])

with Sum instead of Group By in the Total row.
Notice no quotes around the number, as Galaxiom indicated.

Does that help?

Also, it's fairly suspicious that you'd have a field called "Wk 2 Total", unless it's in a query that calculates values per week of the month or year. Are you storing a calculated value in a table and have spaces in a table's field name? If so, those are both bad practices that are likely to cause you headaches later.

 

[~Rachelle~]

Thank you Marla, a light is coming on. Ok, I think I get it. I am probably going to break it while trying to fix it, though. It is fed from a query that is based on (some) calculated table fields. I believe I need to do all the calculations, then, in the query and not in the table. (?) (Am I on the right track with my thinking?)

It hurts to be a noob with no training, lol. My brain hurts but I think I understand better. Would I be a jerk to just add some sum fields to the query to add to my report footer and leave my calculated table fields alone? I hope that makes sense.

 

[Galaxiom]

You can derive a field with:
SomeName: IIf([Wk 2 Total]+[H2]>245,245,[Wk 2 Total]+[H2])

It will calculate that for each row.

Then if you want to sum all the records, a textbox in the footer or header can have the ControlSource:
=Sum(SomeName)

 

[MarlaC]

Thank you Marla, a light is coming on. Ok, I think I get it. I am probably going to break it while trying to fix it, though. It is fed from a query that is based on (some) calculated table fields. I believe I need to do all the calculations, then, in the query and not in the table. (?) (Am I on the right track with my thinking?)

It hurts to be a noob with no training, lol. My brain hurts but I think I understand better. Would I be a jerk to just add some sum fields to the query to add to my report footer and leave my calculated table fields alone? I hope that makes sense.

Rachelle,
You're welcome. No, you would not be a jerk to do that. Queries (and code, and sometimes controls on forms or reports) are the place to do your calculations. Table fields are rarely a good place to store calculations, and never when they are this simple. We were all beginners at some point, so just think of it as a learning opportunity if you can.

 

[~Rachelle~]

HALLELUJAH!!!!!! YES! No longer >> :banghead:. This is terrific. I have really learned a lot with this database. Bad rookie will not put calc fields in tables anymore. Lesson learned.

Thanks so much Galaxiom and MarlaC for the hints/guidance. I am an instructor in a completely unrelated field (obviously, lol!) and I know sometimes it is hard to break it down super, super simple. Many people won't take the time. I appreciate that you did so. Have a great day.

 

[MarlaC]

Rachelle, I'm glad to be able to help. I can't count the number of times I've banged my head over some frustration as I've learned my way along with Access, VBA, and SQL, but one of my favorite things about what I do is that I'm still learning, 16 years after I first started. New problems require new ways of thinking.