DateDiff #error

[wynstonh]

Hi,
I am using the DateDiff function and getting #error when the 2nd date is 31/12/9999 (I know this is the problem but that is how my source data is presented to me).
What I'd like to do is replace the #error with 0.

The formula I have is:

Months: DateDiff("m",[Installation Start Date],[Installation End date]+1)

Any advice much appreciated.

 

[plog]

You need to test for bad values then. In english this is what I would do:

Find the monthly difference between the Start Date and--- if the End date is not a date the Start Date: but if it is a date, the End Date + 1 day.

Convert that sentence to code and you have it.

 

[jdraw]

What exactly is the meaning of 9999 in your business?

 

[wynstonh]

It means an infinite end date.

 

[wynstonh]

As more background. I have 5 queries which I am then putting together in a union query to display all the results on one tab. I'm getting a data type mismatch due to the #error results.

 

[plog]

. I have 5 queries which I am then putting together in a union query

This sounds like a hack around not being able to write one efficient query. Are all 5 queries ultimately based on the same table(s)? If so, you probably just need to write one efficient query to find all your data.

 

[wynstonh]

Nope, 5 queries on 5 different tables.

 

[wynstonh]

You need to test for bad values then. In english this is what I would do:

Find the monthly difference between the Start Date and--- if the End date is not a date the Start Date: but if it is a date, the End Date + 1 day.

Convert that sentence to code and you have it.

Problem with this is 31/12/9999 is a date according to IsDate function

 

[plog]

In that case I would explicitly test for that value using direct comparison instead of the IsDate function.

 

[wynstonh]

messy hack but this worked for me

Months: IIf([Installation End date]>(Now()+100000),"0",DateDiff("m",[Installation Start Date],[Installation End date]+1))

 

[wynstonh]

it'll last 200 yrs and that's good enough for me

 

[plog]

Are you sure its that specific date causing the error and not Null or non-date values?

 

[wynstonh]

yes, definitely that date. I checked for Null values 1st and found none and iif(IsDate( ---- returned TRUE for 31/12/9999

thanks for your advice though, it got me moving in the right direction