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.

"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)

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.

"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)

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.

"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)