[MS Access 2003 SQL] Switch is causing #error, why and how can I fix it...

Question

I have 3 fields in my table: start, end (dates) and length (number, might be blank).
My Aim is to calculate an end date using start and length where end doesn't exist...

I have:

SELECT Switch((g.length<>0) And IsDate(g.end),DateAdd("m",g.length,g.start)) AS field FROM table g

If there is no start, end or length, Access displays blank - this is fine. If there is no end, but start and length are ok, the calculated date is shown - again fine. BUT If there is no end, or length, but a start exists, access displays #Error

I don't understand why, and can't fix it, please help!

Answer 1

If there is no end, but start and length are ok, the calculated date is shown

Are you certain about that point? When I try your query with values for start and length, but no value for end, I get a Null for "field".

Also, you're calling the DateAdd function when this condition is True:

g.length<>0) And IsDate(g.end)

In order for that condition to be True, g.end would have to already contain a valid date ... but I thought you didn't want to perform the calculation when you already have a value for g.end. I'm confused.

Let's try a different approach. If this query returns what you want, good. If not help us understand why it is incorrect.

SELECT
    d.start,
    d.end,
    d.length,
    IIf(IsDate(d.end), d.end,
    IIf(Nz(d.length)>0, DateAdd("m", d.length, d.start), Null)) AS field
FROM table AS d;