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]

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]

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.