Access 2000 OnChange Event ignores a space

[seahorse298]

I am using MS Access 2000 on a Windows XP machine.

I have a list form, listing the number of companies a business is dealing with. I have a filter field on the form which is used to filter the list as characters are typed in the filter field, using the OnChange event for the field.

Generally the filter works fine except when a company name contains a space. The space seems to get ignored in the code for the OnChange event. Ihave checked the field length of the text entered and the last character. The space is there before the filter is performed. After the filter is completed the space seems to be dropped from the filter field. If another character is typed in the field it sits right behind the last character before the space. Therefore, the filter no longer works properly.

This is the code used in the OnChange event:

Me.Filter = "[Supplier Name] Like '" & SupplierFilter.text & "*'"
Me.FilterOn = True
Me!RecCount = Forms![frm Payables List].RecordsetClone.RecordCount
If Me!RecCount = 0 Then ' Checks for empty filter result
Exit Sub
Else
SupplierFilter.SelStart = 10 ' Position to the last good character
End If

Any help that anyone could offer is much appreciated, even if only to confirm that a blank cannot be part of a filter.

[MMcCarthy]

Try this ...

Me.Filter = "[Supplier Name] Like '" & Me!SupplierFilter.Value & "*'"
Me.FilterOn = True
Me!RecCount = Forms![frm Payables List].RecordsetClone.RecordCount
    If Me!RecCount = 0 Then         ' Checks for empty filter result
   Exit Sub
Else
   SupplierFilter.SelStart = 10  ' Position to the last good character
End If
 

Mary