If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems filtering recordset with combobox
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. |
#2
|
|||
|
|||
Problems filtering recordset with combobox
"hedgracer" wrote in message
... 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) |
#3
|
|||
|
|||
Problems filtering recordset with combobox
On Mar 30, 2:41*pm, "Dirk Goldgar"
wrote: "hedgracer" wrote in message ... 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. |
#4
|
|||
|
|||
Problems filtering recordset with combobox
"hedgracer" wrote in message
... 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 befo 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) |
#5
|
|||
|
|||
Problems filtering recordset with combobox
On Mar 30, 3:04*pm, "Dirk Goldgar"
wrote: "hedgracer" wrote in message ... 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 befo 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. |
#6
|
|||
|
|||
Problems filtering recordset with combobox
"hedgracer" wrote in message
... 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. And the recordsource of the form? 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. Hmm. That doesn't really make sense to me as being the result of that one change. You aren't working in an ADP, are you? After you have assigned to the form's .Filter property based on a combo box value of "02/28/201", please verify and post the value of the .Filter property. Does it make any difference if you change the code to: Me.FilterOn = False Me.Filter = "[Month] = '" & Me.cboMthSelection & "'" Me.FilterOn = True ? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|