Switch is causing #error, why and how can I fix it

I have 3 fields in my table: start, end (dates) and length (number, might be blank).

My Aim is to calc开发者_JAVA技巧ulate 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!

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;