A "days at" query

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.

I know I can get the information from scrolling the records, but I'd like to do it purely in sql so my report doesn't have to do its own sorting.

Thanks! Received on Wed Jan 30 2002 - 20:51:14 CST