DateDiff on blank fields?

[PsyberFox]

Hi,

I have a query calculating months between certain dates... I have six date
fields, 3 start dates and 3 end dates. Not all 6 are complete for all
records, and some records have e.g. not the last start / end dates. When I do
a datediff calculating the months of the different date ranges (add result of
each datediff together to get the total number of months of all ranges), it
does not calculate for records where any of the start / end date is blank...
how do I get around this?

Thank you!

 

[Brendan Reynolds]

Hi,

I have a query calculating months between certain dates... I have six date
fields, 3 start dates and 3 end dates. Not all 6 are complete for all
records, and some records have e.g. not the last start / end dates. When I
do
a datediff calculating the months of the different date ranges (add result
of
each datediff together to get the total number of months of all ranges),
it
does not calculate for records where any of the start / end date is
blank...
how do I get around this?

Thank you!

You can use the NZ function to replace the null values with something else.
For example, NZ([SomeField, Date()) would return the value of SomeField if
SomeField contains a non-null value. It would return the current date if
SomeField contains a null value. You'll have to decide what you should
replace the missing values with, depending on what makes sense in the
context of your application. What do you want the difference between a known
date and an unknown date to be?

 

[PsyberFox]

Hi Brendan,

I'll give this a bash... thank you.

I didn't mean that it should return a result between a known date and an
unknown date. What I meant was that e.g. StartDate1 and EndDate1 will have
values, but StartDate2 and EndDate2 might not have values... now if I add
datediff(m,StartDate1,EndDate1) + datediff(m,StartDate2,endDate2) then it
returns a null...

Thank you again!

Werner

Hi,

I have a query calculating months between certain dates... I have six date
fields, 3 start dates and 3 end dates. Not all 6 are complete for all
records, and some records have e.g. not the last start / end dates. When I
do
a datediff calculating the months of the different date ranges (add result
of
each datediff together to get the total number of months of all ranges),
it
does not calculate for records where any of the start / end date is
blank...
how do I get around this?

Thank you!

You can use the NZ function to replace the null values with something else.
For example, NZ([SomeField, Date()) would return the value of SomeField if
SomeField contains a non-null value. It would return the current date if
SomeField contains a null value. You'll have to decide what you should
replace the missing values with, depending on what makes sense in the
context of your application. What do you want the difference between a known
date and an unknown date to be?

 

[Brendan Reynolds]

Hi Brendan,

I'll give this a bash... thank you.

I didn't mean that it should return a result between a known date and an
unknown date. What I meant was that e.g. StartDate1 and EndDate1 will have
values, but StartDate2 and EndDate2 might not have values... now if I add
datediff(m,StartDate1,EndDate1) + datediff(m,StartDate2,endDate2) then it
returns a null...

OK, but then the question becomes what is the difference between one unknown
date and another unknown date? If StartDate2 and EndDate2 are both unknown,
then what should the difference between them be? If the answer is zero, then
NZ(DateDiff("m", StartDate2, EndDate2), 0) should work, I think - I'm going
from memory, though, I haven't tested that expression.

 

[Klatuu]

That is correct.

OK, but then the question becomes what is the difference between one
unknown date and another unknown date? If StartDate2 and EndDate2 are both
unknown, then what should the difference between them be? If the answer is
zero, then NZ(DateDiff("m", StartDate2, EndDate2), 0) should work, I
think - I'm going from memory, though, I haven't tested that expression.