S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 6, 2008 #1 I am trying to remove characters. For example, I am trying to only retreive the record before the (). Can anyone help? 16061279455(HKHKG-USATL)
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 Smeghead Local time Today, 09:18 Joined Jan 12, 2001 Messages 32,059 Jun 6, 2008 #2 How about: Left([YourFieldName],Instr(1,[YourFieldName],"(")-1)
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 6, 2008 #3 Thanks Thanks, that worked!
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 17, 2008 #5 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)
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 Smeghead Local time Today, 09:18 Joined Jan 12, 2001 Messages 32,059 Jun 17, 2008 #6 Where are you using it?
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 17, 2008 #7 In a query... simple query.
boblarson Smeghead Local time Today, 09:18 Joined Jan 12, 2001 Messages 32,059 Jun 17, 2008 #8 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.
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.
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 17, 2008 #9 DB Uploaded in query tbl_adm_data query...Master Bill Attachments hp_control tower project1.zip hp_control tower project1.zip 242.1 KB · Views: 118
boblarson Smeghead Local time Today, 09:18 Joined Jan 12, 2001 Messages 32,059 Jun 17, 2008 #10 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: Code: MasterBill: IIf(InStr(1,[Master_Bill],"(")=0,[Master_Bill],IIf(Len([Master_Bill] & "")=0,"Blank",Left([Master_Bill],InStr(1,[Master_Bill],"(")-1)))
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: Code: MasterBill: IIf(InStr(1,[Master_Bill],"(")=0,[Master_Bill],IIf(Len([Master_Bill] & "")=0,"Blank",Left([Master_Bill],InStr(1,[Master_Bill],"(")-1)))
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 17, 2008 #11 Thanks THANKS very much.
boblarson Smeghead Local time Today, 09:18 Joined Jan 12, 2001 Messages 32,059 Jun 17, 2008 #12 Glad we could get you working there
S scgoodman scgoodman Local time Today, 12:18 Joined Jun 6, 2008 Messages 87 Jun 18, 2008 #13 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?
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?