Easy,Obvious Im sure

[Jerry Stoner]

=IIf([PaymentMethodID]="6",[Text64]-[Total Amount],"0"
Returns correct data when placed in the detail section of the report but 0 only when in the Report footer. I know I am missing something obvious here but am stumped at the end of a long day. What do I need to do to get the function to work in report footer?

 

[pcs]

long day here too...

you are missing a closing paren...but the problem is probably with [Total Amount]. if [Total Amount] is based on something like [field1] + [field2] + [field3], replace the IIf field [Total Amount], with [field1] + [field2] +[field3]. in other words repeat the calc in the footer.

hth,
al

 

[Jerry Stoner]

pcs tried that and it didn't work, the missing paranthesis is a typo in my post not the database.
Pat, the function is not a single record.
PaymentMethodID refers to how payment is made. ID 6 is out of pocket. I am subtracting out of pocket expenses from total expenses to determine how much if any money is owed to an employee after submitting an expense report. Again the function works in detail section but I need it to be shown in Footer.

 

[Jerry Stoner]

Just read my last post and realized how rude it sounds. Sorry. Still looking for help on this one. Would it be better to use two tables, one for for out of pocket expenses and one for company paid expenses? Seems like it would make reporting easier.

 

[Rich]

Try Sum(Iif([PaymentMethodID]= 6,[Text64]-[Total Amount],0))


[This message has been edited by Rich (edited 01-23-2002).]

 

[Jerry Stoner]

Sum(Iif([PaymentMethodID]= 6,[Text64]-[Total Amount],0))
Returns message box indicating there is an extra ")" in expression but I sure don,t see it???

 

[Rich]

I'm sure it's not this simple but did you put
= before the Sum etc, or is 6 a number or text?

 

[Jerry Stoner]

I tried with and without the =. As for the 6 it is a number - actually the PK of tblPaymentMethod - field name Out Of Pocket.
Thanks for the response, wondering if I should chuck this approach and set up seperate tables for Out Of Pocket expenses and Company paid expenses?
There is no deadline for this as I am doing an expenses database just for practice and to learn more about VBA.

 

[Rich]

What is [Text64]is that the name of your field or the textbox name, is it a calculated control?

 

[Jerry Stoner]

[Text64] is the Text Box name and is in the report footer section. It is a calculated control =Sum([Total Of Amount])
derived from using the summing option in the report wizard.It is summing another calculated field called Total Of Amount (again derived from the wizard) in the detail section that calculates the sum of 7 Text boxes. All work correctly including Text64.
Also to explain more fully the report is based on a crosstab query which is based on a select query joining two tables. Expense types to the left and days of the week across the top.There is a field [PaymentMethodID] WHICH WHEN =6 (out of pocket I need to subtract from the total in the report footer to find total company paid expenses.

[This message has been edited by Jerry Stoner (edited 01-24-2002).]

 

[Rich]

Therein lies your problem, you can't Sum calculated fields in this way. I'm just about to rush out for the evening so I can't help at the minute. There are examp in the Help if you can find them, if not I look back later.

 

[Jerry Stoner]

Thanks Rich for staying with me. Been
through help and though I'm sure it's there can't find it. Looking forward to hearing you'r explanation/solution.

 

[Jerry Stoner]

OK Figured it out. I can't have the calc in the footer section because I am subtracting the total from itself and always will return 0. If in detail section it works because I am then subtracting the value "out of pocket" from the total if "expense ID is = to 6 and 0 from total if "expense ID is not 6. Repeating the calc in the footer (yes I tried that days ago still - and should - return 0. Thank you all for your time.Off to find a work around.

 

[Rich]

If you want to send me a copy I'll take a look at it for you this weekend. It needs to be zipped and in 97, please compact it first.
HTH

 

[Jerry Stoner]

Rich, would love to. I dont see your email in your profile.Im at jstoner@nanotronusa.com
Will have to convert from 2000 to 97 but should be no problem. Could you shoot me your email address to the above?

 

[Rich]

That would help

richiet@lineone.net

 

[Jerry Stoner]

Thanks, on its way

 

[Fornatian]

Rich,

I see you're risking putting your email out again - once smitten...

 

[Jerry Stoner]

I certainly won't abuse it. One time use only. Hope no one else does.

 

[Fornatian]

Jerry,

I wasn't for a second suggesting you would, I don't know if you were around a while ago but somebody WAS abusing it and Rich was forced to take his email of the site.

My comments were not aimed at you, just jesting with Rich.

Ian