LEFT() in Query.....

[jock95]

I have a query that is looking up the first part of a string in a field. The first part is separated by a "-"

i.e. Books - Number 7 DOC 6180

Due to the fact that the first part can be any length, I just need the first part of that field.

I wrote this below, and it works for the BOOKS one...but what am I missing?

Left([Cell_Loc],6)

 

[traingamer]

try

Left([Cell_Loc],InStr([Cell_Loc], '-'))

which finds the position of the first '-'

traingamer

 

[jock95]

I am getting an error telling me it is too complicated??

SELECT MASTER.Order_Num, MASTER.Order_Date, MASTER.Cust_Date, MASTER.Ship_Date, MASTER.Run_Date, MASTER.[IMP], Left([Cell_Loc],InStr([Cell_Loc],'-')) AS Cell, MASTER.STATUS, MASTER.Quantity, MASTER.Customer, MASTER.HOT, MASTER.COLOR1, MASTER.COLOR2, QRY_CELLS.PPM, MASTER.Cancel
FROM MASTER INNER JOIN QRY_CELLS ON MASTER.Cell_Loc = QRY_CELLS.CELL_EQUIP
WHERE (((MASTER.Ship_Date) Is Null) AND ((MASTER.Run_Date)=[Requested Run Date?]) AND ((Left([Cell_Loc],InStr([Cell_Loc],'-')))=[Forms]![DASHBOARD]![Combo69]) AND ((MASTER.Cancel)=0));


Is something wrong?

 

[traingamer]

Check all of the parentheses in your where clause

traingamer

 

[grande]

I think it should be like this:
AND (((Left([Cell_Loc],InStr([Cell_Loc],'-')))=[Forms]![DASHBOARD]![Combo69])

-------------------------
Just call me Captain Awesome.

 

[jock95]

This did not work
IS there an easier method?

 

[traingamer]

WHERE (((MASTER.Ship_Date) Is Null) AND ((MASTER.Run_Date)=[Requested Run Date?]) AND ((Left([Cell_Loc],InStr([Cell_Loc],'-')))=[Forms]![DASHBOARD]![Combo69]) AND ((MASTER.Cancel)=0))[COLOR=red])[/color];

traingamer

 

[GingerR]

My turn!

Looks like it's text?

If so, need single quotes around Forms!Dashboard!Combo69...





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

SELECT MASTER.Order_Num, MASTER.Order_Date, MASTER.Cust_Date, MASTER.Ship_Date, MASTER.Run_Date, MASTER.IMP,
Left([Cell_Loc],InStr([Cell_Loc],'-')-1) AS Cell,
MASTER.STATUS, MASTER.Quantity, MASTER.Customer, MASTER.HOT, MASTER.COLOR1, MASTER.COLOR2, QRY_CELLS.PPM, MASTER.Cancel
FROM MASTER INNER JOIN QRY_CELLS ON MASTER.Cell_Loc = QRY_CELLS.CELL_EQUIP
WHERE MASTER.Ship_Date Is Null AND MASTER.Run_Date = [Requested Run Date?]
AND MASTER.Cell_Loc Like "'" & [Forms]![DASHBOARD]![Combo69] & ' -*' AND MASTER.Cancel = 0;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244