Remove Character String

[scgoodman]

I am trying to remove characters. For example, I am trying to only retreive the record before the (). Can anyone help?
16061279455(HKHKG-USATL)

 

[boblarson]

How about:

Left([YourFieldName],Instr(1,[YourFieldName],"(")-1)

 

[scgoodman]

Thanks

Thanks, that worked!

 

[boblarson]

 

[scgoodman]

When I use this...I get a data type mismatch in criteria expression.

What I am trying to delete is every thing after the first ().
I only want to show the #. For example below.

Currently: 16030078016(CNPVG-HKHKG), (HKHKG-USLAX)
What needed: 16030078016

I tried this below...please help.
Left([Master_Bill],InStr(1,[Master_Bill],"(")-1)

 

[boblarson]

Where are you using it?

 

[scgoodman]

In a query...
simple query.

 

[boblarson]

Can you post the db? (see here for how: http://www.access-programmers.co.uk/forums/showthread.php?t=140587 )

I don't see anything that should be causing you a problem.

 

[scgoodman]

DB Uploaded

in query tbl_adm_data query...Master Bill

 

[boblarson]

Well, it goes in the FIELD area, not the criteria:

MasterBill: Left([Master_Bill],InStr(1,[Master_Bill],"(")-1)

But you will see that there are errors when there is nothing there so modify to:

MasterBill: IIf(InStr(1,[Master_Bill],"(")=0,[Master_Bill],IIf(Len([Master_Bill] & "")=0,"Blank",Left([Master_Bill],InStr(1,[Master_Bill],"(")-1)))

 

[scgoodman]

Thanks

THANKS very much.

 

[boblarson]

Glad we could get you working there

 

[scgoodman]

Question

Another questions in regards to this ...

What about if something is separated by a semicolon:
KA; CX

Where in the string does it change?