Problems filtering recordset with combobox
in [Form]
|
From: hedgracer on 30 Mar 2010 15:04 I have a combobox which has the following under the RowSource property: SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month; There is only one column in the combobox which is populated with data from the month column of the Onyx_Allocation table. The data is as follows: 01/31/2010 02/28/2010 I have the following code under the AfterUpdate in the combobox: Private Sub cboMthSelection_AfterUpdate() Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'" Me.Form.FilterOn = True End Sub The problem is that when I select 01/31/2010 one or two rows of 02/28/2010 show up. The same thing happens when I select 02/28/2010 (one or two rows of 01/31/2010 show up). What is causing this? Any help is appreciated. Thanks.
From: Dirk Goldgar on 30 Mar 2010 15:41 "hedgracer" <d.christman(a)sbcglobal.net> wrote in message news:a8483cfe-e0b0-462c-8654-0919e4d7414c(a)33g2000yqj.googlegroups.com... >I have a combobox which has the following under the RowSource > property: > > SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY > Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month; > > There is only one column in the combobox which is populated with data > from the month column of the Onyx_Allocation table. The data is as > follows: > > 01/31/2010 > 02/28/2010 > > I have the following code under the AfterUpdate in the combobox: > > Private Sub cboMthSelection_AfterUpdate() > > Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'" > Me.Form.FilterOn = True > End Sub > > The problem is that when I select 01/31/2010 one or two rows of > 02/28/2010 show up. The same thing happens when I select 02/28/2010 > (one or two rows of 01/31/2010 show up). What is causing this? Any > help is appreciated. Thanks. What data type is this "Month" field? If it's a date/time field, you probably need to use a filter expression like this: Me.Form.Filter = "[Month] = #" & Me.cboMthSelection & "#" "Month" is a bad name for a field, by the way, because it's the same as the Month() function, and that can lead to confusion if you aren't very careful. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: hedgracer on 30 Mar 2010 15:48 On Mar 30, 2:41 pm, "Dirk Goldgar" <d...(a)NOdataSPAMgnostics.com.invalid> wrote: > "hedgracer" <d.christ...(a)sbcglobal.net> wrote in message > > news:a8483cfe-e0b0-462c-8654-0919e4d7414c(a)33g2000yqj.googlegroups.com... > > > > > > >I have a combobox which has the following under the RowSource > > property: > > > SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY > > Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month; > > > There is only one column in the combobox which is populated with data > > from the month column of the Onyx_Allocation table. The data is as > > follows: > > > 01/31/2010 > > 02/28/2010 > > > I have the following code under the AfterUpdate in the combobox: > > > Private Sub cboMthSelection_AfterUpdate() > > > Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'" > > Me.Form.FilterOn = True > > End Sub > > > The problem is that when I select 01/31/2010 one or two rows of > > 02/28/2010 show up. The same thing happens when I select 02/28/2010 > > (one or two rows of 01/31/2010 show up). What is causing this? Any > > help is appreciated. Thanks. > > What data type is this "Month" field? If it's a date/time field, you > probably need to use a filter expression like this: > > Me.Form.Filter = "[Month] = #" & Me.cboMthSelection & "#" > > "Month" is a bad name for a field, by the way, because it's the same as the > Month() function, and that can lead to confusion if you aren't very careful. > > -- > Dirk Goldgar, MS Access MVP > Access tips:www.datagnostics.com/tips.html > > (please reply to the newsgroup)- Hide quoted text - > > - Show quoted text - Month is a varchar(50) field. It is part of a linked table on sql server 2005. sorry I didn't explain that.
From: Dirk Goldgar on 30 Mar 2010 16:04 "hedgracer" <d.christman(a)sbcglobal.net> wrote in message news:87592b69-a01e-49c9-8414-cc9a8def86f9(a)k13g2000yqe.googlegroups.com... > Month is a varchar(50) field. It is part of a linked table on sql server > 2005. sorry I didn't explain that. I see. Then my earlier suggestion won't work, and it's not clear to me why you are getting some unexpected records in the results. Your original filter string looks reasonable. You do have an unnecessary ".Form" qualifier: >> Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'" >> Me.Form.FilterOn = True In this context, "Me" is the form, so you just need Me.Filter = "[Month] = '" & Me.cboMthSelection & "'" Me.FilterOn = True However, I don't think that has anything to do with the problem. When you drop down the combo box, do you see dates that are formatted exactly as you posted before: > > 01/31/2010 > > 02/28/2010 That is, are they always formatted MM/DD/YYYY, with 2 digits for month and day, and 4 digits for year? Have you verified that the data in the [Month] field in the form's recordsource -- a text field, so far as Access should be concerned -- is also formatted exactly the same way? Is the form based directly on the table, or is its recordsource a query? If a query, what is the SQL of the query? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: hedgracer on 30 Mar 2010 16:33
On Mar 30, 3:04 pm, "Dirk Goldgar" <d...(a)NOdataSPAMgnostics.com.invalid> wrote: > "hedgracer" <d.christ...(a)sbcglobal.net> wrote in message > > news:87592b69-a01e-49c9-8414-cc9a8def86f9(a)k13g2000yqe.googlegroups.com... > > > Month is a varchar(50) field. It is part of a linked table on sql server > > 2005. sorry I didn't explain that. > > I see. Then my earlier suggestion won't work, and it's not clear to me why > you are getting some unexpected records in the results. Your original > filter string looks reasonable. You do have an unnecessary ".Form" > qualifier: > > >> Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'" > >> Me.Form.FilterOn = True > > In this context, "Me" is the form, so you just need > > Me.Filter = "[Month] = '" & Me.cboMthSelection & "'" > Me.FilterOn = True > > However, I don't think that has anything to do with the problem. > > When you drop down the combo box, do you see dates that are formatted > exactly as you posted before: > > > > 01/31/2010 > > > 02/28/2010 > > That is, are they always formatted MM/DD/YYYY, with 2 digits for month and > day, and 4 digits for year? > > Have you verified that the data in the [Month] field in the form's > recordsource -- a text field, so far as Access should be concerned -- is > also formatted exactly the same way? > > Is the form based directly on the table, or is its recordsource a query? If > a query, what is the SQL of the query? > > -- > Dirk Goldgar, MS Access MVP > Access tips:www.datagnostics.com/tips.html > > (please reply to the newsgroup) The drop down on the combobox has the dates formatted exactly like I posted them. The data in the Month field in the form's recordsource is the same format as the format in the combobox. The data in the combobox is from a query in the RowSource property that I posted earlier. I removed the Form as you stated. Now the row count is okay for 01/31/2010 (no 02/28/2010 bleeding through) but there is still 01/31/2010 rows bleeding through when 02/28/2010 is selected in the combobox. |