Calculate Dates with If Then Else Statements

[Guest]

I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25
years, etc.) in an Access 2003 database form. This is what I am trying to
do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one is
entered, or NOW, if there is no end date. I have tried many combinations,
but can’t get it to work. Is there a better way to do this? I haven’t had
much experience writing these types of statements. HELP!

 

[Jeff Boyce]

Take a look at the IIF() function, if you are working in a query.

Look into the If...Then...Else statement if you are working in code behind
the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for your quick reply. Unfortunately, I never learned how to write
code, and wasn't able to get an IIF() function to work on this. I think I am
missing a step.

 

[Jeff Boyce]

Check Access HELP on the IIF() function. Embed it in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you for your quick reply. Unfortunately, I never learned how to
write
code, and wasn't able to get an IIF() function to work on this. I think I
am
missing a step.

I need to calculate total years' full time experience (i.e. 2.5 years,
3.25
years, etc.) in an Access 2003 database form. This is what I am trying
to
do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one
is
entered, or NOW, if there is no end date. I have tried many
combinations,
but can't get it to work. Is there a better way to do this? I haven't
had
much experience writing these types of statements. HELP!

 

[John W. Vinson]

I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25
years, etc.) in an Access 2003 database form. This is what I am trying to
do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one is
entered, or NOW, if there is no end date. I have tried many combinations,
but can’t get it to work. Is there a better way to do this? I haven’t had
much experience writing these types of statements. HELP!

I'd use the NZ() function:

DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.

NZ will check the first argument - FullTimeEndDate - and return its value if
there is one, and if not it will return the second argument, Date() in this
case. Now() doesn't return today's date, but rather the current date and time
accurate to microseconds - you don't need that level of precision!

John W. Vinson [MVP]

 

[Jeff Boyce]

Nice touch, John! Using Nz() to handle IIF() functionality!!

Jeff

I need to calculate total years' full time experience (i.e. 2.5 years,
3.25
years, etc.) in an Access 2003 database form. This is what I am trying to
do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one
is
entered, or NOW, if there is no end date. I have tried many combinations,
but can't get it to work. Is there a better way to do this? I haven't
had
much experience writing these types of statements. HELP!

I'd use the NZ() function:

DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.

NZ will check the first argument - FullTimeEndDate - and return its value
if
there is one, and if not it will return the second argument, Date() in
this
case. Now() doesn't return today's date, but rather the current date and
time
accurate to microseconds - you don't need that level of precision!

John W. Vinson [MVP]

 

[John W. Vinson]

Nice touch, John! Using Nz() to handle IIF() functionality!!

IIf(<condition just checks for Null>, <use NZ>, <use the full IIF>)


John W. Vinson [MVP]

 

[Guest]

THANK YOU! That worked perfectly. I didn't know about NZ - it is great!
This is the first time I used this forum - you and Jeff were a big help.

Thanks again!
**********

I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25
years, etc.) in an Access 2003 database form. This is what I am trying to
do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one is
entered, or NOW, if there is no end date. I have tried many combinations,
but can’t get it to work. Is there a better way to do this? I haven’t had
much experience writing these types of statements. HELP!

I'd use the NZ() function:

DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.

NZ will check the first argument - FullTimeEndDate - and return its value if
there is one, and if not it will return the second argument, Date() in this
case. Now() doesn't return today's date, but rather the current date and time
accurate to microseconds - you don't need that level of precision!

John W. Vinson [MVP]