where DATE is BETWEEN startdate and enddate...how?

N

Nathon Jones

Hi,

I need a little help with a SELECT statement.

I have an events table in an Access db. The events have a start date and an
end date.

What statement do I need to show events that occur:

i. on or after the event start date
ii. on or before the event end date, but NOT prior to the start date

In Access I have got a simple query that shows me everything that happens on
or after "today" which looks like this:

Field: eventstartdate
Criteria: >=Now()-1

I know it must be some kind of "between" command....!
Any help would be greatly appreciated. Thanks.

Nath.
 
J

Jerry Porter

A simpler expression for "on or after today would be: >=Date()
Now() includes the date and time, while Date() is just the date. (This
works if your data is just date, with no time).

If all your event fields are just dates (not time), then the following
should work for your two conditions:
i. >= StartDate
ii. Between StartDate and EndDate

Jerry
 
J

Jerry Porter

A simpler expression for "on or after today would be: >=Date()
Now() includes the date and time, while Date() is just the date. (This
works if your data is just date, with no time).

If all your event fields are just dates (not time), then the following
should work for your two conditions:
i. >= StartDate
ii. Between StartDate and EndDate

Jerry
 
N

Nathon Jones

Hi Lynn,

Thanks for replying.

I just tried that, and I changed one of the events to:
start date: 05/03/2005
end date: 05/03/2005

....yet it still appears in this query:

Between (Now() -1) And [EventEndDate]

....which has confused me! :(

Any thoughts?

Thanks again for all your help.
Nath.

Lynn Trapp said:
Try putting the following in your criteria:

Between (Now() -1) And [EventEndDate]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Nathon Jones said:
Hi,

I need a little help with a SELECT statement.

I have an events table in an Access db. The events have a start date and
an
end date.

What statement do I need to show events that occur:

i. on or after the event start date
ii. on or before the event end date, but NOT prior to the start date

In Access I have got a simple query that shows me everything that happens
on
or after "today" which looks like this:

Field: eventstartdate
Criteria: >=Now()-1

I know it must be some kind of "between" command....!
Any help would be greatly appreciated. Thanks.

Nath.
Click to expand...
Click to expand...
 
L

Lynn Trapp

Well, 5/3/2005 is between Yesterday (Now() -1) and 5/3/2005 because the
between operator is inclusive

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Nathon Jones said:
Hi Lynn,

Thanks for replying.

I just tried that, and I changed one of the events to:
start date: 05/03/2005
end date: 05/03/2005

...yet it still appears in this query:

Between (Now() -1) And [EventEndDate]

...which has confused me! :(

Any thoughts?

Thanks again for all your help.
Nath.

Lynn Trapp said:
Try putting the following in your criteria:

Between (Now() -1) And [EventEndDate]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Nathon Jones said:
Hi,

I need a little help with a SELECT statement.

I have an events table in an Access db. The events have a start date
and an
end date.

What statement do I need to show events that occur:

i. on or after the event start date
ii. on or before the event end date, but NOT prior to the start date

In Access I have got a simple query that shows me everything that
happens on
or after "today" which looks like this:

Field: eventstartdate
Criteria: >=Now()-1

I know it must be some kind of "between" command....!
Any help would be greatly appreciated. Thanks.

Nath.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

suggest you start by not using Now(). the Now() function returns the current
date, hour, minute and second in "real" time. i'm guessing that your start
date field is date only, but the time is *not* absent - it defaults to
12:00:00 AM. so if you run your query at 10:05:39 AM today, for example, the
query won't pull today's dates because 12:00:00 AM is less than, *not* equal
to or greater than, 10:05:39 AM today. that's why your expression
=Now()-1
Click to expand...

has to subtract a day from today, in order to pull today's dates. instead,
suggest you try using the Date() function, which returns today's date with
the default time value 12:00:00 AM.

the Between...And operator is inclusive. it's works the same as using
=SomeValue And <=SomeValue
Click to expand...

general tips are all i can give you, i'm afraid, because i don't understand
just what you're trying to accomplish. you say you have an events table;
each record (each event, i assume) has a start date and an end date. then
you say you need to find events that occur between the event start date and
the event end date. huh?? if each event has a start and end date, then that
event occurs...between those two dates, right?
i'm wondering if, instead, you're tracking some kind of large event, such as
a gymnastics meet, where certain individual events are scheduled to run over
a number of consecutive days *within the timeframe of the entire large
event*, with various events overlapping. if this is correct, then maybe
you're wanting to out what events are running *today*? or maybe what events
are running between today and the end of the "meet"?

if you need more help, post back with a more in-depth explanation of what
you're trying to do.

hth
 
D

Douglas J. Steele

Um, where, Lynn? Since Access assumes mm/dd/yyyy, 5/3/2005 would be 3 May,
2005.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Lynn Trapp said:
Well, 5/3/2005 is between Yesterday (Now() -1) and 5/3/2005 because the
between operator is inclusive

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Nathon Jones said:
Hi Lynn,

Thanks for replying.

I just tried that, and I changed one of the events to:
start date: 05/03/2005
end date: 05/03/2005

...yet it still appears in this query:

Between (Now() -1) And [EventEndDate]

...which has confused me! :(

Any thoughts?

Thanks again for all your help.
Nath.

Lynn Trapp said:
Try putting the following in your criteria:

Between (Now() -1) And [EventEndDate]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Hi,

I need a little help with a SELECT statement.

I have an events table in an Access db. The events have a start date
and an
end date.

What statement do I need to show events that occur:

i. on or after the event start date
ii. on or before the event end date, but NOT prior to the start date

In Access I have got a simple query that shows me everything that
happens on
or after "today" which looks like this:

Field: eventstartdate
Criteria: >=Now()-1

I know it must be some kind of "between" command....!
Any help would be greatly appreciated. Thanks.

Nath.
Click to expand...
Click to expand...
Click to expand...
 
L

Lynn Trapp

Yeah, but since the Between operator is inclusive, it will include 3 May,
2005 in a query for records Between Now() -1 And #5/3/2005#.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Douglas J. Steele said:
Um, where, Lynn? Since Access assumes mm/dd/yyyy, 5/3/2005 would be 3 May,
2005.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Lynn Trapp said:
Well, 5/3/2005 is between Yesterday (Now() -1) and 5/3/2005 because the
between operator is inclusive

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Nathon Jones said:
Hi Lynn,

Thanks for replying.

I just tried that, and I changed one of the events to:
start date: 05/03/2005
end date: 05/03/2005

...yet it still appears in this query:

Between (Now() -1) And [EventEndDate]

...which has confused me! :(

Any thoughts?

Thanks again for all your help.
Nath.

Try putting the following in your criteria:

Between (Now() -1) And [EventEndDate]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Hi,

I need a little help with a SELECT statement.

I have an events table in an Access db. The events have a start date
and an
end date.

What statement do I need to show events that occur:

i. on or after the event start date
ii. on or before the event end date, but NOT prior to the start date

In Access I have got a simple query that shows me everything that
happens on
or after "today" which looks like this:

Field: eventstartdate
Criteria: >=Now()-1

I know it must be some kind of "between" command....!
Any help would be greatly appreciated. Thanks.

Nath.
Click to expand...
Click to expand...
Click to expand...
 
Top