need help with query

[gls858]

Don't need a make table query! Just add an additional criterion on the field

LIKE "??*"

John W. Vinson [MVP]

Well I said it was round about While I work with queries quite a bit
most of the criteria I use are pretty simple. My skills with SQL are
limited. I really appreciate the help you both are giving me.

gls858

 

[gls858]

If you are getting an Invalid Procedure error then
-- are you running this query in Access?

-- have you checked your references?

-- will you post the SQL of the query that you are trying to use?

The len of your item numbers should not generate an invalid procedure call
UNLESS you have an ItemNumber that consists solely of the Letters EA or CT.

Try the following, where the match is based on at least one character
followed by CT or EA.

SELECT ItemNumber
FROM YourTable
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM yourtable
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
WHERE ItemNumber Like "*?EA" or ItemNumber LIKE "*?CT"

Query is being run in Access

References in order are
Visual Basics fro Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

No refs marked as missing

No items that are jusr EA or CT but I do have a number of items
that are only one digit

I cut and pasted your code and put my table name in but I'm getting a
syntax error(missing operator). Code below:

SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM ECNIMaster
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"
WHERE ItemNumber Like "*?EA" or ItemNumber LIKE "*?CT"

 

[John Spencer]

My error, there is an extra line in there

SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM ECNIMaster
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"

I was cutting and pasting and obviously pasted one too many times. See if
the above (without the extra WHERE line) works.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gls858]

My error, there is an extra line in there

SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM ECNIMaster
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"

I was cutting and pasting and obviously pasted one too many times. See if
the above (without the extra WHERE line) works.

Thanks for the help. I still have to test your query. I won't be able to
test now until Monday. Taking off a little early today and won't be back
until Monday. But ain't giving up yet

gls858

 

[gls858]

My error, there is an extra line in there

SELECT ItemNumber
FROM ECNIMaster
WHERE ItemNumber In (
SELECT Left(ItemNumber,Len(ItemNumber)-2)
FROM ECNIMaster
WHERE ItemNumber LIKE "*?EA" OR ItemNumber LIKE "*?CT")
OR ItemNumber LIKE "*EA" OR ItemNumber LIKE "*CT"

I was cutting and pasting and obviously pasted one too many times. See if
the above (without the extra WHERE line) works.

John,
Finally got back to this and tested your query.

It seems to work once I took out the items consisting of just 2 digits.
With the 2 digit items in the table I was still getting illegal procedure.

It takes quite a while (5 minutes) and then displays the results. When I
try to scroll or export the results, Access seems to lock up or at least
it's processing so slow it seems to be locked up. The table contains
about 50,000 items. Right now I'm trying to export the list to Excel and
the process has been running for about 20 minutes and the progress bar
appears to have stopped.

gls858