Problem with Sum iff on Date

[Sharon Hague]

Hi All

I have a report with reads from a query.

In my query I have 4 fields - a date field, week day, salary month and Overtime. I have created my week day field from my date field so that it displays the day of the week.

In my report I have created a text box in the footer to be able to sum all overtime on Sat.

My Sum iif reads as follows:- =Sum(IIf([Week Day]="Sat",[Overtime],0))

However, when I try and run the report I get a message saying This expression is typed incorrectly, or it is too complicated to be evaluated etc.

Can anybody advise what I am doing wrong and how I can get around this?

Cheers.

 

[Rich]

WeekDay is looking for a numeric value, 6 ?
sorry read it wrongly, but WeekDay is a function you can use against your original date field

 

[Sharon Hague]

Hi Rich

Thanks for your reply but I can't get it to work when I change "Sat" to 6 by using "6" or [6] etc. It comes up with a message saying that "The expression you entered contains invalid syntax" etc.

Any ideas?

 

[Sharon Hague]

Rich

Forget my previous note. There was something wrong with the text box.

I have created a new text box and my control source is now =Sum(IIf([Week Day]=6,[Overtime],0))

When the report is run all Overtime shows a total as zero even when an entry is in Sat. If I change 6 to "6" it brings the same message as I first had.

Any ideas? - This won't be anything to do with blank entries will it?

Please advise.

 

[Rich]

[Week Day] is a field that you have created, I don't know its format so I can't help, but WeekDay is an Access function
something like
Sum(Iif (WeekDay([OriginalDateField]) = 6,[SomeField],0)

 

[Sharon Hague]

Rich

I created my own field called "Week Day" in my query so that it is easy for me to see what day of the week it is against what date I enter in my "MyDate" field. Therefore in my query all I have done is given a column a name - in this case I have called it Week Day so in my field it reads Week Day:[MyDate].

Therefore in my report I have 4 text box's - "MyDate", "Week Day", "Salary Month" & "Overtime". When run, this lists around 30 items i.e from 01.08.03 - 31.08.03 depending on the salary Month parameter entered.

So, I have now tried your code and in my control source it reads:- =Sum(IIf(Weekday([Week Day])=6,[Overtime],0)).

When I run the report it shows a figure which is not relevant to Overtime on Saturdays. I have also tried the above by entering [MyDate] instead of [Week Day] and it shows the same irrelevant figure.

I'm puzzled as to why it isn't working

Any ideas?

 

[Rich]

You don't need the [Week Day] in the query, just add the MYDate field to the report again and set the format for the control on the property sheet to ddd or dddd. And it's 7 for Saturday, sorry.
=Sum(IIf(Weekday([MyDate])=7,[Overtime],0)).

 

[Sharon Hague]

Thanks Rich

It's now worked.

Quick question - With Satuday being "7" is Sunday "1"?

 

[Rich]

Yes, unless you change it.

 

[Sharon Hague]

Rich

Thank for your reply.

Only just recevied it as I have been off for a couple of days.

Cheers