How to Get Left portion of String in Query 1

[uncleG]

Hi All,
I am stumped again
Starting from the left I need to locate the first " ", or "_", or "-", or "."
Then return all Characters before it.
The engineers have promised not to add any more variances.

Using Access 2003
Sample Data in [tblItems].[BPNo]:

B21821 Outline.pdf
B21617D_Outline_7BS-896.pdf
21978_OUTLINE 792.pdf
2264101- Outline 5OU-983.pdf
A2376501B Outline YTO-756.pdf
A13893.pdf

From the above sample entries I would like to return in my Query the following:

B21821
B21617D
21978
2264101
A2376501B
A13893

Thanks,
UncleG

 

[PWise]

Select left(bpno,instr(bpno," ")-1)
from tablename
union
Select left(bpno,instr(bpno,"_")-1)
from tablename
union
Select left(bpno,instr(bpno,".")-1)
from tablename

 

[Golom]

You could try a UDF

Public Function GetName(myfield As String) As String
    GetName = Split(Split(Split(Split(myfield, " ")(0), "_")(0), "-")(0), ".")(0)
End Function

 

[uncleG]

Thanks for the input.
Pwise,
I replaced tablename with the table name in a new query and it wouldn't run. Also tried the 4 statements independently in the query grid and yeilded multiple results.

Golom,
I added the Public Function and changed GetName to GetBPName at both locations. This also errored out so I added an error statement and the query ran but produced an error on every line.
Error 0 happens on every line where the BPNo "is not null" clicking OK allows the routine to continue and the desired value is returned.
Error 9 occurs on every line where the BPNo is blank.

Any further Thoughts, I am missing something I'm sure?
Thanks Again
UncleG

 

[PHV]

You may try this variation from Golom's code:

Public Function GetBPName(myfield) As String
    GetBPName = Split(Split(Split(Split(myfield & "", " ")(0), "_")(0), "-")(0), ".")(0)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uncleG]

Thanks PHV,
You have done it again.
For others up against the same type of issue.
In my query I had to add and IIf Trim Statement to handle the nulls, empty, zls and or Blanks which were throwing errors.

BPNo2: IIf(Trim([BpNo] & "")="","",GetBPName([BpNo]))

The above function is much cleaner then the following:

SELECT tblItems.BPNo,IIf(Trim([BpNo] & "")="","",Replace([BPNo],"_"," ")) AS BpNo1, Replace([BPNo1],"-"," ") AS BpNo2, Replace([BPNo2],"."," ") AS BpNo3, IIf(Trim([BpNo3] & "")="","",(Left([BpNo3],InStr([BpNo3]," ")-1))) AS BPNo4 FROM tblItems

Thanks
UncleG