Access 2003, Applying Filter in ADP

[Guest]

I have created an ADP connected to SQL Server 2000.
I Open a table in grid view with Access 2003 SP1 and select the form-based
filter.
Entering a value >30 in the 4th Field I expect to see all records with the
value greater than 30 in Field 4 (Field4>30). Instead I get all records with
Field1>30.
All Filters entered allways apply to the first field in the record. Why that?
The same effect is also shown in Access 2002 but not Access 2000.
Also MDBs do not show this strange effect.

Doing the same with a form:
create a new form
select the table as a recordsource
add all fields and change the form for grid view
in grid view select the form-based filter and add a filter in Field4 (>30)
apply the filter, you will get the wrong resultset (Field1>30)
go back to design view and inspect the filter property
you should see ((Table1.Field4=30))
now just edit the filter property in design view !
remove the last ) and reenter it
go to grid view and you will see the correct result (Field4>30)

Could it be that this error only applies to the german version of Access
2003 or do you experience the same effect?
Thank you for reporting your experience.

 

[Michael Cheng [MSFT]]

Hi Hubi,

Thanks for your post.

From your descriptions, I understood that your Filter for Access ADP will
be applied to the wrong column. Have I understood you? Correct me if I was
wrong.

Unfortuantely, I was not able to reproduce it on my side. Would you please
send me a small sample to reproduce? (using Northwind in SQL Server or
generate SQL scripts of the database specified). Send them to
(e-mail address removed) (remove online as it's only for SPAM)

Additionally, the document below illustrated how to filter the records in
Access correctly and hope it helps

How to filter records in an Access database
http://support.microsoft.com/?id=304259

Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center

 

[Guest]

Hi Michael

Thank you for your assistance.
Trying to reproduce the behavior in Northwind I found a special circumstance
when this occurs:
In many of our tables the first field has the same name as the table.

To reproduce this try the following in Northwind:

CREATE TABLE [dbo].[AnyProducts] (
[AnyProducts] [int] NOT NULL PRIMARY KEY,
[ProductName] [nvarchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[UnitPrice] [money] NULL ,
[UnitsInStock] [smallint] NULL ,
[UnitsOnOrder] [smallint] NULL ,
[ReorderLevel] [smallint] NULL ,
[Discontinued] [bit] NOT NULL
) ON [PRIMARY]
GO

GRANT SELECT , INSERT , DELETE , UPDATE ON [AnyProducts] TO [public]
GO

INSERT INTO AnyProducts SELECT * FROM Products
GO

Opening the table in ADP the filter always applies to the field with the
same name as the table, independent of its position in the structure (in this
example AnyProducts).

Greetings
Hubi

 

[Michael Cheng [MSFT]]

Hi Hubi,

Thanks for your further detailed information!

I did reproduce it on my side and we are working further to find the root
cause and to see whether it is necessary to report this to the development
team. I will keep you updated as soon as there is anything valueable to
add.

Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center