substring query

[hfs]

i have lotnumber column in lot table ,and excelPath column in location table,
lotnumber data is the substring of excel path column in location table,so i want a query in ms access which shows excelpath against lot number ..i dont know how to create that query..need help with that:banghead:

 

[pr2-eugin]

Without any example of what the data looks like, a specific answer is not possible.. Lookup on String manipulation functions.. Like Mid, Instr..

 

[hfs]

Expr 1: Mid([ExcelPathLocation],InStrRev([ExcelPathLocation],"("))

(access)


sqlView

(SELECT Mid([ExcelPathLocation],InStrRev([ExcelPathLocation],"(")) AS [Expr 1]
FROM ExcelLocation;


)

I have this expression,but its not giving the value i want!

I want [COLOR=red](new1234)[/COLOR] without brackets from the below data:
 
 
ExcelLocationExcelPathLocationC:\Users\Desktop\Testing\[COLOR=red](new1234)[/COLOR]_BagGla_20130925.xlsm

Thanks In advance

 

[pr2-eugin]

If that's the case try Replace.

? Replace(Replace("C:\Users\Desktop\Testing\(new1234)_BagGla_20130925.xlsm","(", ""), ")", "")
C:\Users\Desktop\Testing\new1234_BagGla_20130925.xlsm