What id do is have a 3 column list box, with only one column visible. The three columns would be the description and the start & before / cutoff dates.
When populating the list box set it to a value list.
Set the list using vba
Dim strValueList as string
dim Tomorrow as string
Tomorrow = format(datedadd("d",1,date()), "#yyyy/mm/dd#;")
`use the date() function to get todays date as opposed to the now() function which gets the current date and time. Ive used dated add to set the end filter to be tomorrow, you could choose a different value
StrValueList = "All;#1900/01/01#;" & tomorrow
` i tend to use ISO dates to avoid confusion between date formats
`01 jan 1900 is the start for vba dates
Strvaluelist = strvaluelist & "Today;" & format(date(),"#yyyy/mm/dd#;") & tomorrow
`this defines the start and cut off date
'the# symbol delimits a date literal
...add others as required
'eg last seven days including today
Strvaluelist = strvaluelist & "Last 7 days;" & format(dateadd("d",-7,date()),"#yyyy/mm/dd#;") & tomorrow
if you want the last week or month then you are going to have to do a bit more work to define this week, last week, this month, last month and so on
Then in the list box 'on click' event id set up the filter
You need to find the code to pull the right row and columns
Column(0) is the description
1 is the start date
2 is the before / cutoff date.
Offhand i think its mylistbox.selected.column(index) but im not certain
Lets assume it is
Me.filter = "mydatecolumn >= column(1) & " and mydatecolumn <" & .column(2)
'one of thje reason youneed to use the cutoff date is that ACCESS stores date and times int he same variable when usign the datatype datetime. so if you want a sepcific days data then youneed to specify thge time band
10 oct 2013 00:00:00 to 10 oct 2013 23:59:59 and use between
10 oct 2013 >= date < 11 oct 2013. the implicitly they are the same but are easlier to code to.
You will need to replace mylistbox and mydatecolumn with the names of your listbox and column
As ever this is air code, it almost certainly has errors both in logic and typos. Its almost certainly includes vba property errors, but it should point you in the right direction
Last edited by healdem; 10-10-13 at 05:33.
I'd rather be riding on the Tiger 800 or the Norton