I have a query with 5 fields and 327 records. Among these fields is a unique code that identifies each record. I want to filter this query to show only 55 of these 327 records. I tried adjusting the criteria in the ‘code’ field so that it would only show ‘"#####" and “#####”… ####55"’ but when I ran the query it said it was too complex. Is there any other way to amend this query to only show the 55 records that I wish for it to show? I have the codes in a text file.
What is the criterion used to define the records you want? Do all the records have something in common (for example, in the primary key, do they all start with a certain letter?)?
Oblong, I want to help, but I’m not sure I understand your question. When you say, “I want to filter this query to show only 55 of these 327 records,” I assume you have some criteria to differentiate those 55 records, right?
Or do you just want to show the first 55 records in the DynaSet?
It sounds like your text file contains a list of the codes you want displayed, not necessarily in order.
If this assumption is correct, I would import your text file into a table, call it ‘requiredcodes’ or something, and name the field that contains the list: ‘thecodesIwant’. I’ll call your main table ‘stuff’ Then, in query designer, add the ‘stuff’ table, and add the following into the criteria line for the ‘code’ field:
In (select distinct thecodesIwant from [requiredcodes])
Is your query based on multiple tables (i.e., are there any JOINS involved in your query)? If so, does the query work properly without your criterion?
If you’re NOT writing SQL, you’re probably using the normal builder-thingy. Don’t forget that in the “Criteria” grid below, everything on the same row for your different fields results in an AND, and everything in the same column for a single field results in an OR. If you have other criteria for other fields, try moving the criteria down a few cells in the grid.
Are you doing something in a single grid cell like this:
> 15 AND < 255
If you’re dealing with something that’s NOT a number, try creating an invisible calculated field that turns your field into a number. For example, say your field is CODE and is a four digit number prefixed with a non-numeric digit, e.g., “A1234”. Add a dummy field like CODE_NUM: VAL(MID(CODE,2,4))
This will give you a strict numberic result that you can them apply criteria to.
I’m not sure I understand what it is you are doing. Are the code numbers in sequence? Could you use the Between X And Y statement in a query? There are other ways to skin the cat, if you give a bit more on how the code is assigned and describe the 55 records you need a bit more, I (or probably someone else with more experience) should be able to help.
Sorry for not being clearer. Mavpace, that was it. I knew it was simple. Thank you very much. You have no idea how much this helps.
The codes were just a unique identifier to each record. It wasn’t defined as the primary key. I needed to narrow down this query that already existed to show only those records that had these codes, which I had gotten from another source.