ACCESS FEED: Re: [MS_AccessPros] Re: Filter list box by combo

Jumat, 24 Februari 2012

Re: [MS_AccessPros] Re: Filter list box by combo

Duane,

Can you take a look at it, I have added your code but now its displaying dates in the combo.

I have uploaded to the needs assistance folder.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: Duane <duanehookom@hotmail.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, February 24, 2012 12:58 PM
Subject: [MS_AccessPros] Re: Filter list box by combo

Art,
I assume you might want to do this in other forms (applications). I would write a function and call it in the On Open of the form:

Public Function GetDateRowSource() As String
'1;"This week";
'2;"Next two weeks";
'3;"This month";
'4;"This quarter";'
'5;"This calendar year";
'6;"Next 12 months";
'7;"All appointments"
'code to return a row source of a 4 column listbox or combo box
' the 3rd and 4th column are the start and end dates of the range
' columns are numbered beginning with 0
Dim strRowSource As String
strRowSource = "1;'This week';'" & _
Date - Weekday(Date) + 1 & "';'" & Date - Weekday(Date) + 7 & "';"
strRowSource = strRowSource & "2;'Next two weeks';'" & _
Date - Weekday(Date) + 1 & "';'" & Date - Weekday(Date) + 14 & "';"
' more code here for other
GetDateRowSource = strRowSource
End Function

In the On Open event, use code like:
Me.cboTimePeriods.RowSource = GetDateRowSource
Then you can change the Row Source of the list box with code in the after update of cboTImePeriods:

Me.lstAppointments.RowSource = _
"SELECT [AppointmentID],[AppointmentDate], [AppointmentDesc] " & _
"FROM tblAppointments " & _
"WHERE AppointmentDate Between #" & Me.cboTimeperiods.COlumn(2) & _
"# AND #" & Me.cboTimePeriods.Column(3) & "#;"

Duane

--- In MS_Access_Professionals@yahoogroups.com, "Art" <dbalorenzini@...> wrote:
>
> I have a list box called lstAppointments and I would like to filter it by using a combobox called cboTimePeriods. I am using the following values in the combobox.
>
> 1;"This week";2;"Next two weeks";3;"This month";4;"This quarter";5;"This calendar year";6;"Next 12 months";7;"All appointments"
>
> This is recordsource for the lisbox:
> SELECT [tblAppointments].[AppointmentID], [tblAppointments].[AppointmentDate], [tblAppointments].[AppointmentDesc] FROM tblAppointments;
>
> What would I need to do to get the filter working?
>
> Thanks,
>
> Art Lorenzini
> Sioux Falls, SD
>

[Non-text portions of this message have been removed]