Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

LEFT() in Query.....

Status
Not open for further replies.

jock95

Programmer
Joined
Feb 19, 2005
Messages
7
Location
US
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)
 
try
Code:
Left([Cell_Loc],InStr([Cell_Loc], '-'))
which finds the position of the first '-'

traingamer
 
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?
 
Check all of the parentheses in your where clause

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

-------------------------
Just call me Captain Awesome.
 
This did not work
IS there an easier method?
 
Code:
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top