Getting something completed following Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

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])))))
 
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])))))
 
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!)


KARL DEWEY said:
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])))))

JOM said:
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: " &
Click to expand...
Click to expand...
Abs(Sum(IIf([TaxDocStatus]="Cancelled",DatePart("m",DateValue([TxDocDateRcvd
]))>DatePart("m",DateValue([RqstDateRcvd])))))
 
Thanks alot, I tried it and it worked....

KARL DEWEY said:
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])))))

JOM said:
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])))))
Click to expand...
Click to expand...
 
Back
Top