 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Trim(Left( -- what if no spaces?
I'm using the following which is working very well except it not working if there are no spaces in the text of the field. Can someone help? MAILING_ADDRESS_2: Trim(Left([Mailing_Address2],InStr(1,[mAILING_aDDRESS2]," "))) & " " & (Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," "))) Thank you. Try MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 ,[Mailing_Address2],Trim(Left([Mailing_Address2],InStr(1, mAILING_aDDRESS2]," "))) & " " & (Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," "))))  SignatureGood Luck BS"D > I'm using the following which is working very well except it not working if > there are no spaces in the text of the field. Can someone help? [quoted text clipped - 4 lines] > > Thank you. Thanks Ofer. Unfortunately, I keep getting an Invalid Syntax message when I try to click out of this field after pasting. Can you add what's missing??? Maria > Try > [quoted text clipped - 11 lines] > > > > Thank you. MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 ,[Mailing_Address2] ,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," "))) & " " & Trim(Mid([mailing_address2],Instr(1,[Mailing_Address2]," ")+1)) ) IF this gives you trouble, then try breaking this down into steps IIf(Instr([Mailing_Address2]," ")=0,[Mailing_Address2], "Has Space") If that works, then add to it MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 ,[Mailing_Address2] ,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," ")))) If that works, then add a bit more. > Thanks Ofer. > [quoted text clipped - 26 lines] >> > >> > Thank you. Worked great! Thank you so much! Maria > MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 > ,[Mailing_Address2] [quoted text clipped - 39 lines] > >> > > >> > Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.