Getting something completed following Month

[Guest]

I have the following statment that checks on requests completed the following
month from the from the rqstdatercvd... The thing is that if something came
in on December and was completed in JAnuary, its does not give me a value,
which is very logical that 12 is greater than 1... How do I take care of this?

="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("m",DateValue([TxDocDateRcvd]))>DatePart("m",DateValue([RqstDateRcvd])))))

 

[Guest]

Try this - UNTESTED.
="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("yyyy",DateValue([TxDocDateRcvd]))&DatePart("m",DateValue([TxDocDateRcvd]))>DatePart("yyyy",DateValue([RqstDateRcvd]))&DatePart("m",DateValue([RqstDateRcvd])))))

 

[Douglas J Steele]

Or, perhaps a little cleaner:

="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",Format([TxDocDateRcvd],"yyyymm")>Form
at([RqstDateRcvd], "yyyymm"))))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this - UNTESTED.
="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("yyyy",DateValue([TxDocDateR
cvd]))&DatePart("m",DateValue([TxDocDateRcvd]))>DatePart("yyyy",DateValue([R
qstDateRcvd]))&DatePart("m",DateValue([RqstDateRcvd])))))

I have the following statment that checks on requests completed the following
month from the from the rqstdatercvd... The thing is that if something came
in on December and was completed in JAnuary, its does not give me a value,
which is very logical that 12 is greater than 1... How do I take care of this?

="Cancelled Requests Cancelled in the Following Month: " &

Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("m",DateValue([TxDocDateRcvd
]))>DatePart("m",DateValue([RqstDateRcvd])))))

 

[Guest]

Thanks alot, I tried it and it worked....

Try this - UNTESTED.
="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("yyyy",DateValue([TxDocDateRcvd]))&DatePart("m",DateValue([TxDocDateRcvd]))>DatePart("yyyy",DateValue([RqstDateRcvd]))&DatePart("m",DateValue([RqstDateRcvd])))))

I have the following statment that checks on requests completed the following
month from the from the rqstdatercvd... The thing is that if something came
in on December and was completed in JAnuary, its does not give me a value,
which is very logical that 12 is greater than 1... How do I take care of this?

="Cancelled Requests Cancelled in the Following Month: " &
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("m",DateValue([TxDocDateRcvd]))>DatePart("m",DateValue([RqstDateRcvd])))))