Query problem (MS SQL and MS Access tables)

[Peter Vroomen]

L.S,

I have a front-end application (MS Access 2000) that uses data from both MS
Access tables and MS SQL Server tables. The SQL tables are linked to the
front-end application via ODBC. I am trying to perform a query using the
following tables:

1. dbo_430_PRProject (MS SQL Server Table)
2. dbo_430_cicmpy (MS SQL Server Table)
3. tbl_ink_bb (MS Access table)
4. tbl_ink_bb_status (MS Access table)

Table …
 ‘1’ contains information about our projects (Customer-ID, Project
description, start date, end date etc.)
 ‘2’ contains information about our business contacts (both customers and
suppliers)
 ‘3’ contains information about the orders we have placed with our
suppliers (Supplier ID, Order form ID, Order date, Status ID, etc.)
 ‘4’ contains the description of the order status (i.e. Status ID ‘2’ means
‘Order placed with supplier’).

The SQL tables linked from ‘Exact Globe 2003’ the accounting software that
we use.

I have a query that combines fields from all of the above tables. The query
itself works fine, but unfortunately I am only able to apply a filter to
fields from the SQL tables (be it ‘Docmd.ApplyFilter , strfil’ via VBA or a
filter used directly in the query screen of Access). No records are returned
as soon as I apply a filter on MS Access tables. Is there a logical
cause/solutions for this problem?

Kind regards,


Peter Vroomen

 

[Dale Fye]

Are you trying to filter for a form, or in the query?

As far as I know, and I've got a couple of apps that use both SQL Server
(2000) and Access (2000, XP, and 2003), there should be no reason that
filtering on a field in one of the Access tables should cause a problem. Can
you post the SQL that is not working properly?

Dale

 

[John W. Vinson]

I have a query that combines fields from all of the above tables. The query
itself works fine, but unfortunately I am only able to apply a filter to
fields from the SQL tables (be it ‘Docmd.ApplyFilter , strfil’ via VBA or a
filter used directly in the query screen of Access). No records are returned
as soon as I apply a filter on MS Access tables. Is there a logical
cause/solutions for this problem?

Can you simply use parameters in the criteria of the query, rather than a
Filter?

John W. Vinson [MVP]