How to use text filter in Access with SQL Server database

[Ron]

Hi,

In Access 2007, I have a simple datasheet form that displays records from a
single SQL Server 2005 database table. The form is marked to allow filters
but not to allow adds, changes or deletes, i.e. read-only

If I select any column and use the "Filter" button on the Access toolbar, it
will display the list of values with the checkboxes for selecting a certain
value or group of values. However, if I select, say, only one value, I
receive an error message:

"Cannot apply filter on one or more fields specified in the Filter property".

Similarly, if I choose "Text Filter" and choose, say "Begins With" and enter
a text string, I receive an error:

"Enter a valid value"


This happens in all columns EXCEPT the field which is in the SQL table's
primary key. If I am in the primary key's column, then the filter functions
work normally.

Is this a limitation when filtering on sql server based tables? If I set
the "Order by" property to match an index built on the sql server table
itself, will that allow other fields on the datasheet to be filtered on?

Thx

Ron

 

[Daryl S]

Ron -

My guess is it would have to do with the difference in delimeters for text
and dates between Access and SQL Server. I would expect the filters to work
on all numeric fields. If that turns out to be the case, you may need to
build your own criteria using SQL Server delimeters. Maybe someone has
other ideas?