Re: A "days at" query

Eel wrote:

> Hi,
> I'm trying to create an sql query on an ms-access
> database that will give me the "days at" a particular
> location in a factory. If I have a simple table like:
>
> "create table transactions
> date_in DATE,
> date_out DATE"
>
> I can do something like "select date_in, date_out, date_out - date_in
> as da from transactions"
>
> This works, but I have a more complex requirement which is that in
> cases where date_out = null, ie still at a location, I want to use
> current_date - date_in instead of date_out - date_in. Is there a way
> to do this? I keep getting syntax errors.

There is a standard function COALESCE which returns the first of it's arguments that is not null.
select COALESCE(date_out, SYSDATE) - date_in ....

I'm not sure about the function name and the name of the current date in Access, but you should get the idea.

hth,
Heinz Received on Thu Jan 31 2002 - 09:13:15 CET