Filter by UPC Code

[Djblois]

I have a query looking up Product Information from an ODBC connection. Here is my SQL:

SELECT WAVE3_PC_DL.UPC, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, WAVE3_PC_CATS.CAT_DESC, WAVE3_PC_DL.DEPT, UPC.Date
FROM ((WAVE3_PC_CATS LEFT JOIN WAVE3_PRODS ON WAVE3_PC_CATS.PC_CAT = WAVE3_PRODS.PC_CAT) LEFT JOIN WAVE3_PC_DL ON WAVE3_PRODS.PC = WAVE3_PC_DL.PC) LEFT JOIN UPC ON WAVE3_PRODS.PC = UPC.ITEMNO
ORDER BY WAVE3_PC_DL.UPC;

It works but I want to do two other things:

1) I want to filter it so it only shows the items that their UPC start with "0-71270"

2) I only want to show the 7th - 12th digits of the UPC.

The first one is more important but the second would be really helpful as well.

 

[Sess]

I am not going to try to figure out which is the UPCcode field in your query but when you build your query to just get the 0-71270 part of the database in your select you need to ask for left([UPCcode],7]) and put "0-71270" as the criteria. Also you need to ask for mid([UPCcode],7,6) to get just those digits.

 

[Djblois]

It is saying undefined function "left" in expression

 

[Djblois]

Here is my SQL now:

SELECT Left([UPC],7) AS Filter, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, WAVE3_PC_CATS.CAT_DESC, WAVE3_PC_DL.DEPT, UPC.Date
FROM ((WAVE3_PC_CATS LEFT JOIN WAVE3_PRODS ON WAVE3_PC_CATS.PC_CAT = WAVE3_PRODS.PC_CAT) LEFT JOIN WAVE3_PC_DL ON WAVE3_PRODS.PC = WAVE3_PC_DL.PC) LEFT JOIN UPC ON WAVE3_PRODS.PC = UPC.ITEMNO
WHERE (((Left([UPC],7))="0-71270"));

 

[Sess]

Way wrong. Which field is the UPC code that you want to filter?

 

[Djblois]

It is called UPC from table WAVE3_PC_DL

 

[Sess]

I do not see it in the original code you posted so that is why I am confused then.


SELECT WAVE3_PC_DL.UPC, mid([WAVE3_PC_DL.UPC],7,6) as ShortUPC, WAVE3_PC_DL.UPC, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, WAVE3_PC_CATS.CAT_DESC, WAVE3_PC_DL.DEPT, UPC.Date
FROM ((WAVE3_PC_CATS LEFT JOIN WAVE3_PRODS ON WAVE3_PC_CATS.PC_CAT = WAVE3_PRODS.PC_CAT) LEFT JOIN WAVE3_PC_DL ON WAVE3_PRODS.PC = WAVE3_PC_DL.PC) LEFT JOIN UPC ON WAVE3_PRODS.PC = UPC.ITEMNO
WHERE WAVE3_PC_DL.UPC LIKE "0-71270*"
ORDER BY WAVE3_PC_DL.UPC;

 

[Djblois]

It keeps giving me an error on Mid saying undefined function. Which is weird because I know it is an Access Function.

Here is the SQL now with the like working:

SELECT WAVE3_PC_DL.UPC AS Sequence, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, WAVE3_PRODS.PC_CAT, WAVE3_PC_DL.DEPT, UPC.Date
FROM ((WAVE3_PC_CATS LEFT JOIN WAVE3_PRODS ON WAVE3_PC_CATS.PC_CAT = WAVE3_PRODS.PC_CAT) LEFT JOIN WAVE3_PC_DL ON WAVE3_PRODS.PC = WAVE3_PC_DL.PC) LEFT JOIN UPC ON WAVE3_PRODS.PC = UPC.ITEMNO
WHERE (((WAVE3_PC_DL.UPC) Like "0-71270*"))
ORDER BY WAVE3_PC_DL.DEPT, WAVE3_PC_DL.UPC;

 

[Sess]

I just tried in Access to write a query in design view and when I look at the SQL behind the design it gives me
Mid([Field1],7,6) as SessString
and it works. Are you trying to free hand SQL statements instead of design view with the expression builder?

 

[Djblois]

I tried both ways. and they are both giving me an error.

 

[Sess]

Don know why you are renaming WAVE3_PC_DL.UPC AS Sequence but anyway this below does not work?

SELECT WAVE3_PC_DL.UPC AS Sequence, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, WAVE3_PRODS.PC_CAT, WAVE3_PC_DL.DEPT, UPC.Date, Mid([WAVE3_PC_DL.UPC],7,6) AS ShortUPC
FROM ((WAVE3_PC_CATS LEFT JOIN WAVE3_PRODS ON WAVE3_PC_CATS.PC_CAT = WAVE3_PRODS.PC_CAT) LEFT JOIN WAVE3_PC_DL ON WAVE3_PRODS.PC = WAVE3_PC_DL.PC) LEFT JOIN UPC ON WAVE3_PRODS.PC = UPC.ITEMNO
WHERE (((WAVE3_PC_DL.UPC) Like "0-71270*"))
ORDER BY WAVE3_PC_DL.DEPT, WAVE3_PC_DL.UPC;

Check that you are not missing anything in the ADO DAO library checklist.

 

[Djblois]

Yeah, that keeps telling me undefined function "Mid" in expression

 

[Sess]

Then one of the reference library is not checked and that is how the Mid function does not exist.

 

[Djblois]

do you know which one that is?

 

[Sess]

Sorry I do not and my mentor is away so I can not even ask for you.
This is what MicroSoft suggests for Office2003
http://office.microsoft.com/en-us/access/HP030844291033.aspx

 

[SOS]

Try unchecking the DAO reference and then closing the dialog and going back in and then checking it again from the list (you'll have to scroll down the list a ways to find it).