Query

[Amy998]

In my 2.0 Access database, there is an Aging Query, in this query, there is a formula like this, Age:CInt(Now()-[Date Product/Form Received]), [Date Product/Form Received] is a field name, it contains Null value, when I run the query, the "Age" field has all "#Error" value. I think the Null value in [Date Product/Form Received] cause the problem, I also tried Age: IIf([Date Product/Form Received] Is Null,0,CInt(Now()-[Date Product/Form Received])), but it still doesn't work, I don't know how to solve it, please help me.
Thanks in advance.

 

[Georgews]

Try using the Datediff function in an expression like this
age: IIf([date] Is Null,"",DateDiff("d",[date],Date()))
This will give you the number of days. For days use "d", form months use "m", or for years use "yyyy"
I have just run this over a table with nulls in the [date] field and it gives the age where there is a value and leaves it blank where there is not a value.
If you want the number of days this will be accurate, but for months and years it rounds which will possibly cause incorrect results.

 

[Amy998]

Hi Georgews,
Thank you for the reply.
I tried the formular your provided, it gave me "36863, 36635" in Age(after query, there are only two records), the date for [Date Product/Form received] are "6/25/00, 2/8/01", so i don't know where these numbers came from, Do you know what is wrong?
Thanks
Amy

 

[dneufarth]

try

IIf(IsNull([Date Product/Form Received]),"",DateDiff("d",[Date Product/Form Received],Date()))


Dave

 

[Amy998]

Hi Dave,
Thank you for the reply.
I tried your formular, the results in Age field are "36864, 36636", I was wondering where are thoes numbers came from, I found out the Access 2.0 took the 6/25/00 as 6/25/1900. Now I am in trouble, I don't know if there is a possible to have Access 2.0 convert the date to 2000 year, do you have any idea?
Thanks
Amy