case age

[Guest]

I'm trying calulate the age of cases in the table. Basically, if there is a
close date then I want to calculate close date - received date. If there is
no close date. I want to calculate today - received date. here is what I
have so far

CASE_AGE: IIF([CLOSE_DATE],NULL,DATE -[Date_RECEIVED]),IIF([close_date],not
null,[close_Date] - date_received])

 

[Lynn Trapp]

Try this instead.

CASE_AGE: IIF(IsNull([CLOSE_DATE]),DATE -[Date_RECEIVED],[close_Date] -
date_received])

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Lynn thanks for the quick reply.

I made a couple changes... However when I run it I get an #Error in output
table. Also when I run query it asks for the date. "Date" should be Today
date.... Any suggestions? Below is what I used. Thanks

CASE_AGE: IIf(IsNull([closed_date]),[DATE]-[Date
Received],[closed_date]-[Date Received])

Try this instead.

CASE_AGE: IIF(IsNull([CLOSE_DATE]),DATE -[Date_RECEIVED],[close_Date] -
date_received])

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I'm trying calulate the age of cases in the table. Basically, if there is
a
close date then I want to calculate close date - received date. If there
is
no close date. I want to calculate today - received date. here is what I
have so far

CASE_AGE:
IIF([CLOSE_DATE],NULL,DATE -[Date_RECEIVED]),IIF([close_date],not
null,[close_Date] - date_received])

 

[Lynn Trapp]

Take DATE out of the square brackets and add parenthesies. Like this --
Date()

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Lynn thanks for the quick reply.

I made a couple changes... However when I run it I get an #Error in output
table. Also when I run query it asks for the date. "Date" should be
Today
date.... Any suggestions? Below is what I used. Thanks

CASE_AGE: IIf(IsNull([closed_date]),[DATE]-[Date
Received],[closed_date]-[Date Received])

Try this instead.

CASE_AGE: IIF(IsNull([CLOSE_DATE]),DATE -[Date_RECEIVED],[close_Date] -
date_received])

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I'm trying calulate the age of cases in the table. Basically, if there
is
a
close date then I want to calculate close date - received date. If
there
is
no close date. I want to calculate today - received date. here is
what I
have so far

CASE_AGE:
IIF([CLOSE_DATE],NULL,DATE -[Date_RECEIVED]),IIF([close_date],not
null,[close_Date] - date_received])

 

[John Vinson]

I'm trying calulate the age of cases in the table. Basically, if there is a
close date then I want to calculate close date - received date. If there is
no close date. I want to calculate today - received date. here is what I
have so far

CASE_AGE: IIF([CLOSE_DATE],NULL,DATE -[Date_RECEIVED]),IIF([close_date],not
null,[close_Date] - date_received])

Use the NZ and DateDiff functions instead:

Case_Age: DateDiff("d", [Date_RECEIVED], NZ([Close_Date], Date()))

NZ will return Close_Date if it is not NULL, and the optional second
argument - Date() in this case - if it is. DateDiff will then
calculate the number of days between the two.

John W. Vinson[MVP]

 

[Guest]

Thanks, it worked perfertly... I appreciate the help from both of you.

I'm trying calulate the age of cases in the table. Basically, if there is a
close date then I want to calculate close date - received date. If there is
no close date. I want to calculate today - received date. here is what I
have so far

CASE_AGE: IIF([CLOSE_DATE],NULL,DATE -[Date_RECEIVED]),IIF([close_date],not
null,[close_Date] - date_received])

Use the NZ and DateDiff functions instead:

Case_Age: DateDiff("d", [Date_RECEIVED], NZ([Close_Date], Date()))

NZ will return Close_Date if it is not NULL, and the optional second
argument - Date() in this case - if it is. DateDiff will then
calculate the number of days between the two.

John W. Vinson[MVP]