ACCESS: Calculating the difference between two dates

[Tebbott]

Hi there,

this one should be pretty simple for alot of you but i've been tearing my hair out for a good hour now trying to find the solution online. Basically my problem is this:

I have two fields in my table, startdate and enddate. I need to calculate the number of years between the two dates. I have done this using YearsService: DateDiff("yyyy",[StartDate],[EndDate]).

All working fine, however if there is no EndDate present then no value is displayed. How can i add an extra part to this expression that says, if there is no end date present, use todays date.

Regards

Tebbott

[Tebbott]

Think this is probably the wrong place to post this question in retrospect. I was under the impression that microsoft access uses mySQL (maybe it does, im still unsure) but after reading other posts here it would seem that i am mis-informed. However, i will leave the post up until my suspissions are verified. Never know, the answer might be so obvious to coders that somebody could help me anyway.

Tebb

[guelphdad]

microsoft access and mysql are two entire different database systems.

you may still be able to use COALESCE though. COALESCE says to check for a value in the column and if the value is NULL (note that isn't an empty string like ' ', or '' or a space) then to use something else indicated.

this is how I'd write it in mysql, you can probably adapt it to access:


SELECT
DATEDIFF(COALESCE(enddate,current_date()), startdate)
FROM
yourtablenamehere


so essentially if there is a null in the enddate field then the current_date is calculated instead.