count # for current week

[slimjen]

All, using 2010. I have a report which includes a date field [CallsRecd]. I need to count the #of calls for the current week. Sample Data to illustrate:

Calls
ID    CallsRecd        
1     8/8/2014        
2     8/8/2014        
3     8/15/2014        
4     8/14/2014        
5     8/15/2014

This is what I need to return

 Count  Calls Rec
3

I have a current report but I don't want to group on the CallRecd field because the report is grouped on another field. I tried using in the report detail the dcount function but receiving syntax errors. Help Pls

[June7]

If you run the report on Monday morning, is the 'current week' you want really the week ending Sunday the day before?

Is this report for the 'current week' only? How are you filtering the records?

[slimjen]

The report is run every Friday afternoon after data entry cutoff time. The report is filtered by date on a form.

[June7]

Options for filtering:

1. calculate the week begin and end date range based on the date on form and use BETWEEN AND operator on the CallsRecd field

2. calculate a week number or week end date for each record in query and apply filter criteria to that constructed field, criteria would be calculated week number or week end date of the date on form

[slimjen]

I thought the report had some built in options without using the query like a calculation on the report to get the current week and counting the results. Ok. I used this to calculate the week:

Expr1: DatePart("ww",[Calls Recd])

Now; you say "appy filter criteria to that constructed field, criteria would be calculated week number or week end date of the date on form"? I already have date filter for the report. I don't want to filter but only want to count the records for the current week. The user needs the other weeks on the report?? Maybe Im missing something??

[June7]

Okay, then apply filter in expression on report, maybe:

=Count(IIf([Expr1]=DatePart("ww",[date from form]),1,Null))

or

=Sum(IIf([Expr1]=DatePart("ww",[date from form]),1,Null))

[slimjen]

This gives me dates within the current week without calculating the week number first:

DatePart("ww",[Calls Recd])=DatePart("ww",Date()) And Year([Calls Recd])=Year(Date())

I tried to get a count of the total records with:

=count(iif(DatePart("ww",[Calls Recd])=DatePart("ww",Date()) And Year([Calls Recd])=Year(Date()))

I get wrong number of arguments

[slimjen]

Ok. I got what I needed by using this formula in my query and then applying it to my report as a calculated field:

ThisWeek: DatePart("ww",[CallsRecd])=DatePart("ww",Date()) And Year([CallsRecd])=Year(Date())

The only problem is that when a date is in the current week; it returns -1 I guess equal to Yes. I need it in the report as a 1 so when I use this calculated field; it returns a whole number:

=Sum([ThisWeek])

Right now; this returns -3 instead of 3. Thanks

[June7]

Could wrap expression in Abs() function to always return a positive.

[slimjen]

For some reason this works: =Sum([ThisWk]*-1) Thanks for all your help