How do I filter my recordset so that it returns only results for today ?
Set your filter so that it filters on Date()
You have to do this in the advanced recordset window, say we have a table holding events and these events have dates and we only want to display todays events. In simple mode we created this SQL statement:
then change that in advanced mode into:
WHERE EntryDate = Date()
events being the tablename and EntryDate is the fieldname of our event date/time field.
Test it to confirm it only returns today's entries and that is all there is to it.
Now, if you want to display entries from the next 30 days alter it to something like this:
WHERE EntryDate>=Date() AND EntryDate<Date()+30
ORDER BY EntryDate ASC
Now let's say our client wants a page that shows a list with only the dates that have one or multiple events. We would simply change the query so that it reads:
Then on our "Master" page we would display these in a table and add repeat region to the row holding the dynamic dates that are returned to the page. We then apply a "goto detail page" behavior which we point to a page that lists all values for that specific date. On that page we would then apply another "goto detail page" behavior but we pass the "eventID" to show the details of the event the visitor wants to view, more on using the detail page behavior is found here:
Have fun playing with SQL commands !
Note: Date() as mentioned above is compatible with MS-Access, for SQL Server replace Date() with getDate()