Show only certain records in a query by date and time

[Linda RQ]

Access 2003. I'm sorry to be asking so many questions today but I hope they
help someone else too and I have been working on only access since 7 this
morning. I have been spending a lot of time learning how to find my own
answers using Jeff Conrad's favorite sites, and the help file in access and
I only ask if I have spent more than an hour hunting. I still don't quite
know how to ask some questions yet I guess.

I have a query with a dtt field that has therapy end dates, I have both date
and time in the field. I want my query to show records that do not have an
expiration date in the field and also records that have expiration dates
that haven't occurred yet. I have end dates of
10/20/06,10/21/06,10/22/06,10/23/06,10/24/06, 10/25/06. It sort of works
because the dates 10/20, 10/21,10/22 and 10/23 do not show up but the 10/24
record still shows and it should have expired at 2:14 pm on 10/24 (today)
and since it's 7:35 pm now I was thinking it shouldn't be showing up. This
leads me to believe that I need to put in something for the time factor in
my expression but I can't seem to find anything so far to include the days,
minutes and hours.

The other issue is that I completely am using the wrong expression but I was
pretty excited that it worked almost as expected.

Is Null Or >=DateAdd("d",0,Date())

Thanks,

Linda

 

[Guest]

Hi Linda,

No need to use dateadd()

You could just use is null or >= Date()

However, for your time problem, you should probably use Now() which includes
date and time. ie: is null or >= Now()

Hope that gets you moving in the right direction.

Damian.

 

[John Vinson]

Is Null Or >=DateAdd("d",0,Date())

Make it

Is Null OR >= DateAdd("d", 1, Date())

to find all instances that will expire TOMORROW.

Your expression is looking for all instances which either haven't
expired at all (Is Null) or expired at some time after midnight last
night - either before or after the present moment.

If you want to find all cases which have an expiration date/time in
the future from the present moment, use

= Now() OR IS NULL

John W. Vinson[MVP]

 

[Linda RQ]

Thanks Damian! I had thought about those 2 at one time but for some reason
couldn't figure out that it would work...but seems to work like I need it
to.

Linda

 

[Linda RQ]

Make it

Is Null OR >= DateAdd("d", 1, Date())

to find all instances that will expire TOMORROW.

Your expression is looking for all instances which either haven't
expired at all (Is Null) or expired at some time after midnight last
night - either before or after the present moment.

If you want to find all cases which have an expiration date/time in
the future from the present moment, use



John W. Vinson[MVP]

Thanks, John. Is there any difference/issues between yours this one?
Is Null or >>= Now()

Thanks,
Linda

 

[John Vinson]

Thanks, John. Is there any difference/issues between yours this one?
Is Null or >>= Now()

Thanks,
Linda

Well, other than that an extra > slipped in somehow, no. The OR
operator is commutative so

Is Null OR > Now()

and

Now() OR Is Null

are equivalent expressions.

John W. Vinson[MVP]