Calcuation with IF function? (1 Viewer)

[mariaw]

Hi

I have the following in a text box on a report:

=IIf([ClosingDate] Like "*/05/*",Sum([Expr1]),0)

The data it is analysing is the cost of job advertisements per month. We advertise in the Star, Guardian and Job Centre, so

Expr1 = [Star Cost] + [Guardian Cost]+[Job Centre Cost].

The query also analyses the Closing Date of the advert (the criteria for this is to type in the start and end date).

The Expr1 adds up fine on the query and the report, but when I want it to tell me how much was spent in the month of May (by closing date), I thought the above formula would work...but it doesn't?

Thanks

Maria

 

[Dennisk]

Access reports are restrictive as to where you can place totals. Normayy you can only place a total on the report footer or a group footer. In both cases expand the calculation like this

SUM([Star Cost] + [Guardian Cost]+[Job Centre Cost])

Hint 1: don't use default names in this case Expr1- should be called something like

txtTotalCost

Hint 2: don't use spaces in names

 

[mariaw]

Hi

Ok I have expanded it to this (e.g. to tell me the total costs for May):

=IIf([ClosingDate] Like "*/05/*",Sum([Star Cost]+[Express Cost]+[Ad Mag Cost]+[GuardianCost]+[Opportunities Cost]+[Job Centre Cost]+[Other Cost]),0)

and it still doesn't work? I don't think the spaces are a problem in this cuz they add up fine on the query?

Any other ideas - thanks

Maria

 

[Dennisk]

Create a new text box and paste the calculation in - not the iif statement. This will tell you whether the sum is working or not. Ie to solve a problem break it down into smaller problems. (Spaces are bad practice only as it may cause problems exporting data to other systems eg oracle)

 

[mariaw]

I can confirm the sum is working...so it's something to do with the if part of the calculation....

thanks

Maria

 

[Dennisk]

if you want to filter your report this should be done in the recordsource query for the report.

 

[mariaw]

Sorry no comprendo....how do I do a filter in a query please?

Maria

 

[Brianwarnock]

It's neither the IIf nor the sum, I take it that you have no grouping on your report and that the sum is producing a total for the whole report?

If all of the data is on a Detail line remove the function Sum ie instead of ,Sum(a+b+c) just ,(a+b+c)
also note that the use of Like for dates is not recommended by Microsoft although it does work in some circumstances I would use Month(datefield)=5 to look for May.

Brian

 

[Rich]

You should do some reading up on normalising your db, at the moment it reads like a spreadheet