Date Query II 1

[FMezler]

OK, I posted this one before, but I dont think I was clear in what I was trying to do. I have a Message database and each message has a start date and end date. I would like to make a query that would display the appropiate messages for whatever the CURRENT date is. For example, one message could have a Start Date of 5/14/2002 with an End Date of 5/17/2002. I would want that record displayed on 5/14/2002, but not after 5/17/2002. There could be another record in the table with a Start Date of 5/15/2002 and an End Date of 5/16/2002. That record should be displayed starting 5/15/2002 and I would still want the 5/14/2002 record displayed because it is not due to end until 5/17/2002. Now I know I cant use something like >= Start Date and <= End Date, because with that criteria, if it was the 14th, the message for the 15th would be displayed because that falls true for the criteria. But I dont want the 15th to be displayed on the 14th - not until it is the 15th.

What it all boils down to is having the correct messages displayed for their correct date ranges. That range could be 1 day or 364 days.

 

[mikevh]

How about

SELECT * FROM YOURTABLE
WHERE DATE() >= YOURTABLE.[START DATE] AND
DATE() <= YOURTABLE.[END DATE];

Will that do it?

 

[GoDawgs]

Ok I'm very confused by this one, I think you might be confused yourself...if you use >= start date and <= end date then if today is 5/14 your message from 5/15 won't show up since today is not >= 5/15...so I think you have the right idea, you just tricked yourself somehow. Hope that helps...explain some more if I missed the boat on this one.

Kevin

 

[KyleS]

Couldn't you use something like
I'm trying to recreate the query builder view, so bear with me...

StartDate EndDate
<=Date() >=Date()

This would display the message if the StartDate was today or before (so the messages for the 13th and 14th would show, but not the 15th) and the EndDate was today or beyond (so if it ended today or tomorrow it would be there, but if it ended yesterday it would not)

I may be over-simplifying this, but I think this should work for you... Kyle

 

[sko]

StartDate >= Date() and EndDate <= Date()
Should return all records between those dates if that's what you want.

The Date function returns the current date.

 

[FMezler]

To Mikevh:
The >= Start Date and <= End date does not work because it would include messages with start dates that are ahead of the current date.

To godawgs:
I realize I cant use the >= Start and <=End for the very reason you explain. These are messages that would be entered ahead of time to appear on specific dates. Maybe there is no way to do this, but I would find that hard to believe. Basically I need it to look at each message and say these 2 things:
1. Is the start date for the message equal to todays date? If so, display it.
2. If the start date is not equal to todays date BUT the end date is also not equal to todays date then display the message.

 

[FMezler]

KyleS:
That did it! Simple yes, but I have no clue why I didnt see that. Thank You!!

 

[KyleS]

Happy to help! Kyle

 

[mikevh]

FMetzler: The important thing is that you've got
something that does what you want, but I believe
you're incorrect in saying,&quot;The >= Start Date and <= End date does not work because it would include messages with start dates that are ahead of the current date.&quot;

Here's the sample data that I tried my version of
the query on:

start date end date
7/4/53 5/30/02
12/21/61 5/2/02
5/15/02 5/17/02
6/25/02 6/30/02

SELECT * FROM YOURTABLE
WHERE DATE() >= YOURTABLE.[START DATE] AND
DATE() <= YOURTABLE.[END DATE];

start date end date
7/4/53 5/30/02

The query returns only the first record, because
it has a starting date <= the current date, and an
ending date which hasn't passed yet. It does not
display the last 2 records, which have starting dates
greater than today's date.