Filter a table for use instead of using a query

[Chris Freeman]

I'm looking for an sql string to filter a table before I run a query against.
I read a couple of other posts, and some replied not to do this, but the
reson is that the Address table has over 100,000 address and would required
using two mainframe tables to get the Client ID of the property, and we want
to filter the Client ID before before running the query. In test, the query,
which joins four tables, two of which or mainframe (MF) systems, takes almost
5 mins to run.

The import sheet has the ClientID, but the Holder table does not, so we have
to join Checks to (MF)Holder to (MF)Property to Address to get the Client ID.

In the import process coding, I wanted to take the Client ID on the import
sheet, pull that out through coding, then filter the Address table using that
value from the import sheet and then the query would run against the smaller
table, cutting it down to about 10,000, plus not have to touch the mainframe
tables. I can add Select Case to select the Client > Property ID, since
there's only about 20 clients.

I tried using the follwoing code:
docmd.runsql ("SELECT tbl_address.ClientIdent, tbl_address.City,
tbl_address.State, tbl_address.Zip FROM tbl_address WHERE
(((tbl_address.ClientIdent)="SCI"))") from a test query, but keep getting the
"Expected list separator or )" error message. SCI is the test, this would be
import value in production. Seems simple enough but I'm missing something and
just can't see it.

Thanks in advance

 

[Crystal (strive4peace)]

Hi Chris,

since this:
="SCI"

is INSIDE a string that has double quotes to delimit it, you must do one
of two things:
either
1. ='SCI'
or
2. = ""SCI""

You can also speed things up greatly by linking and filtering on short
fields with an index ... how many characters can ClientIdent be?

also:
docmd.runsql

is used to RUN an action query. If you want to OPEN a Select query, use:

DoCmd.OpenQuery


Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
have an awesome day
*