Problem with search query

[rebfein]

Hi there,



I'm hoping you can help me out here.

I spent the last three months verifying and changing information (by hand) in a database table. I had a query set up to list certain types of parts (i.e. product type 'A' begins with 'CC' followed by a six digit number, etc.).

I need the query to search for product numbers beginning with the letters 'CC', 'CE', 'DR', 'DZ', 'IC', etc. for a total of 8 product types (this expanded from the original 6).

I've expanded the search string as follows:
Like "*" & [CC] & "*" Or Like "*" & [CE] & "*" Or Like "*" & [DR] & "*" Or Like "*" & [DZ] & "*" Or Like "*" & [IC] & "*" Or Like "*" & [QF] & "*" Or Like "*" & [QP] & "*" Or Like "*" & [QS] & "*".

The result is a list of 437 products beginning with CC or CE from a table of 9900+.

What is wrong with the criteria?

Any assistance would be appreciated.

Thank you,

[ranman256]

brackets means a field name, you want field values, use quotes:

[field] like "DZ*"

[CJ_London]

if they always start with CC, whatever, you don't need the initial * - will only slow the query down because it won't be able to use indexing.

What is wrong with the criteria?

you haven't provided the full criteria - what is like....?

and using square brackets implies either you have fields called CC, CE etc or it is looking for either of the characters within the square brackets- see this link https://support.office.com/en-us/art...3-61897c87b3f4. In particular this bit

[ ]

Matches characters within the brackets.

b[ae]ll finds ball and bell, but not bill.

A simpler criteria might be

left(myfield,2) in ('CC','CE'.....)

[rebfein]

I'm trying to create a query of a 9900+ itemized parts database. This query will list 9 sub-categories. Each sub-category begins with a two alpha code (i.e. CC12345, CE45678, CT, DR, DZ, IC, QF, QP & QS are the category codes). There will also be a manufacturer name and part description, pulled from two other tables.

Two joins occurred. One between the Component Status - ACT table and Supplier Information (for Manufacturer's name) and the other, between the

Component Status - ACT table and Component Description table (for each component's description). The search criteria for the 9 types of parts is shown in the query.

Now, when I run the query, all seems to go well, until you notice the total amount of 2010 listed in the bottom, and that the query shows only one QP return result. I did a separate count of the parts and there should be 2643.

How can I fix this?

[CJ_London]

How can I fix this?

did you read my post? I provided a link as to why you don't get the result you want and a solution, which having now seen your data, will work. Just change myfield to [KGS PN]