more efficient way to add lots of query criteria items

[tstew]

Hello,

Sorry for the lousy subject line, couldn't think of something more
descriptive.

I need to filter a database by several criteria. One of the criteria is a
list of zip codes. I have 52 different zip codes I need to filter by and I
need to filter by all of them in one query. They won't change, so I know I
could just enter "91301" or "91302"... etc in either Design View or SQL View.
But this seems like lousy protocol. Is there a more efficient or 'preferred'
way to list all 52 zips?

Thanks,
Mark

 

[Duane Hookom]

I would either create a table of the 52 zipcodes to join to your query or add
a field to your table of unique zipcodes to identify the 52.

 

[tstew]

I thought the table idea might be something to consider. It's a bit beyond my
present knowledge to 'link' a table, but I could probably figure it out.

The extra field idea... could you explain that a bit more. I already have a
zip code field. It seems you are saying 'add another zipcode field'. Is that
the idea? What would that accomplish, and/or how to implement it?

Thanks,
Mark

 

[KARL DEWEY]

Create the table and name ZipFilter with field ZipCode.
If your data has exact matches, only have 5 digit Zip codes, never the
Plus4, you can join the tables.
Open your query in design view, add the ZipFilter table, Click on the Zip
code field of your data table and drag to the ZipFilter ZipCode field.

If you do not have exact matches then add the ZipFilter table but do not
join. Under your data table Zip field in the criteria row enter --
Like [ZipFilter].[ZipCode] & "*"

If your list may change a lots you can add a checkbox to the ZipFilter table
for Active. Then add the ZipFilter.Active field in the design view with
criteria -1 to select only those that are marked.

 

[tstew]

HI Karl,

You helped me out on a couple of issues yesterday. THANKS!

I used the "Join" method. That worked great...but... they are used in a
query that drives a continuous form. With the Join in place, the records are
not editable in the form or the query. If I remove the Join, then I can edit,
but the records are no longer filtered. I can't see where the records are
'locked'. Any ideas how I can use the 'join' and still edit the query results?

Thanks!
Mark

Create the table and name ZipFilter with field ZipCode.
If your data has exact matches, only have 5 digit Zip codes, never the
Plus4, you can join the tables.
Open your query in design view, add the ZipFilter table, Click on the Zip
code field of your data table and drag to the ZipFilter ZipCode field.

If you do not have exact matches then add the ZipFilter table but do not
join. Under your data table Zip field in the criteria row enter --
Like [ZipFilter].[ZipCode] & "*"

If your list may change a lots you can add a checkbox to the ZipFilter table
for Active. Then add the ZipFilter.Active field in the design view with
criteria -1 to select only those that are marked.

--
Build a little, test a little.

I thought the table idea might be something to consider. It's a bit beyond my
present knowledge to 'link' a table, but I could probably figure it out.

The extra field idea... could you explain that a bit more. I already have a
zip code field. It seems you are saying 'add another zipcode field'. Is that
the idea? What would that accomplish, and/or how to implement it?

Thanks,
Mark

 

[tstew]

Hummm, maybe my database is corrupt. If I just have the table "zipfilter"
open in the query (no joins, no links, no fields from the extra table in the
query) I get spurious data. If I stick any other table in the query, I get
spurious data. Does this maybe sound like corruption?

Create the table and name ZipFilter with field ZipCode.
If your data has exact matches, only have 5 digit Zip codes, never the
Plus4, you can join the tables.
Open your query in design view, add the ZipFilter table, Click on the Zip
code field of your data table and drag to the ZipFilter ZipCode field.

If you do not have exact matches then add the ZipFilter table but do not
join. Under your data table Zip field in the criteria row enter --
Like [ZipFilter].[ZipCode] & "*"

If your list may change a lots you can add a checkbox to the ZipFilter table
for Active. Then add the ZipFilter.Active field in the design view with
criteria -1 to select only those that are marked.

--
Build a little, test a little.

I thought the table idea might be something to consider. It's a bit beyond my
present knowledge to 'link' a table, but I could probably figure it out.

The extra field idea... could you explain that a bit more. I already have a
zip code field. It seems you are saying 'add another zipcode field'. Is that
the idea? What would that accomplish, and/or how to implement it?

Thanks,
Mark

 

[John W. Vinson]

Hummm, maybe my database is corrupt. If I just have the table "zipfilter"
open in the query (no joins, no links, no fields from the extra table in the
query) I get spurious data. If I stick any other table in the query, I get
spurious data. Does this maybe sound like corruption?

Try compacting and repairing. Perhaps you could post the definition
(fieldnames and field types) of zipfilter, and some examples of the "spurious
data".