Date driven data display

[Simon Wigzell]

My client has an annual calendar of events consisting of a record for each
event, key field is the event date saved as a date type field. They would
like the display to start with the current month, list to the end of the
year then start the beginning of the year and list up to the current month.
How does one retrieve data based on date? I guess I'd like something like :

SELECT * FROM EVENTS WHERE [month is greater than or equal to the current
month] ORDER BY Date

Then :

SELECT * FROM EVENTS WHERE [month is less than the current month] ORDER BY
Date

What is the syntax for the month bits between the []?

Thanks!

[Dan Brussee]

On Fri, 17 Oct 2003 00:14:17 GMT, "Simon Wigzell"
<simonwigzell@shaw.ca> wrote:

>My client has an annual calendar of events consisting of a record for each
>event, key field is the event date saved as a date type field. They would
>like the display to start with the current month, list to the end of the
>year then start the beginning of the year and list up to the current month.
>How does one retrieve data based on date? I guess I'd like something like :
>
>SELECT * FROM EVENTS WHERE [month is greater than or equal to the current
>month] ORDER BY Date
>
>Then :
>
>SELECT * FROM EVENTS WHERE [month is less than the current month] ORDER BY
>Date
>
>What is the syntax for the month bits between the []?
>
>Thanks!
>

Hints:
The first day of the current month is:
DateSerial(year(now), month(now), 1)

The last day of the previous month one year from now is:
DateSerial(year(now) + 1, month(now) , 0)

SELECT * FROM EVENTS WHERE
EventDate >= 'FirstOfCurrentMonth'
AND
EventDate <= 'LastOfPrevMonthPlus1Year'
ORDER BY EventDate

Another suggestion... Do not name a field "Date".

[Simon Wigzell]

"Dan Brussee" <dbrussee@NOSPAMnc.rr.com> wrote in message
news:kdluovck36fgfahb4su8pcmln5prvs77p2@4ax.com...

> On Fri, 17 Oct 2003 00:14:17 GMT, "Simon Wigzell"
> <simonwigzell@shaw.ca> wrote:
>

> >My client has an annual calendar of events consisting of a record for

each

> >event, key field is the event date saved as a date type field. They would
> >like the display to start with the current month, list to the end of the
> >year then start the beginning of the year and list up to the current

month.

> >How does one retrieve data based on date? I guess I'd like something like

:

> >
> >SELECT * FROM EVENTS WHERE [month is greater than or equal to the current
> >month] ORDER BY Date
> >
> >Then :
> >
> >SELECT * FROM EVENTS WHERE [month is less than the current month] ORDER

BY

> >Date
> >
> >What is the syntax for the month bits between the []?
> >
> >Thanks!
> >

>
> Hints:
> The first day of the current month is:
> DateSerial(year(now), month(now), 1)
>
> The last day of the previous month one year from now is:
> DateSerial(year(now) + 1, month(now) , 0)
>
> SELECT * FROM EVENTS WHERE
> EventDate >= 'FirstOfCurrentMonth'
> AND
> EventDate <= 'LastOfPrevMonthPlus1Year'
> ORDER BY EventDate
>
> Another suggestion... Do not name a field "Date".
>

Thanks, I'll try that, and "Date" was just for the example, thanks.