Need Help on Record Sort Code 1

[proflola]

The Report is suppose to show all calls closed in the current month listed by week but incorrectly includes weeks from past years instead of just 2006.

The Formulas are as follows:

Crystal Month
Month(HFWDatetoDate({CallLog.ClosedDate}))

Current Month
Month(CurrentDate)

Selection Formula:
{CallLog.CallStatus} = 'Closed' and
{@CurrentMonth} >={@Crystal Month} and {@CurrentMonth} <= {@Crystal Month}
and if
{?Customer Type} <> 'All'
then
{CallLog.CustType} = {?Customer Type}
else
{CallLog.CustType} like "*"

Any ideas on how to fix it?

Thanks,

Sandy

 

[mocgp]

You didn't filter for the year. For this you can use

Year(CurrentDate)=2006

Another format is

DatePart("yyyy",CurrentDate)=2006

 

[synapsevampire]

You're overcompkcating this, try:

(
CallLog.CallStatus} = 'Closed'
)
and
(
HFWDatetoDate({CallLog.ClosedDate}) in minimum(monthtodate)
to dateserial(year(minimum(monthtodate)),month(minimum(monthtodate))+1,1)-1
)
and
(
if {?Customer Type} <> 'All'
then
{CallLog.CustType} = {?Customer Type}
else
if {?Customer Type} = 'All' then
true
)

Omit the {CallLog.CustType} like "*", it doesn't do anything.

Not sure why you call this the Crystal Month:

Crystal Month
Month(HFWDatetoDate({CallLog.ClosedDate}))

Looks like the month in a database field to me using a proprietary function.

-k

 

[proflola]

THANKS TO EVERYONE!

I went with Synapsevampire's solution and it worked perfectly!

Whew, you all sure know your stuff! Thanks again!

--Sandy