Ok, I have a field that combined three different names separated by a comma (Test1 X. Test1, Test2 W. Test2, Test3 Q. Test3). Not all have three names though, some has only one and some two. I need to separate or break down this field into three.
so far, i have the following in three different text boxes:
'first
=Left([billtolname],InStr(1,[billtolname],",")-1)
'second
=Trim(Mid([billtolname],InStr(1,[billtolname],",")+1,IIf(InStr(InStr(1,[billtolname],",")+1,[billtolname],",")=0,0,InStr(InStr(1,[billtolname],",")+1,[billtolname],",")-InStr(1,[billtolname],",")-1)))
'third
=IIf(InStr(InStr([billtolname],",")+1,[billtolname],",")<>0,Right([billtolname],Len([billtolname])-InStr(InStr([billtolname],",")+1,[billtolname],",")),Right([billtolname],Len([billtolname])-InStr([billtolname],",")))
It's fine if there are three names, but if the field only has one name, i get an #error on the first box.
Can anyone tell me how to build a better trap for this mouse?
Thank You
so far, i have the following in three different text boxes:
'first
=Left([billtolname],InStr(1,[billtolname],",")-1)
'second
=Trim(Mid([billtolname],InStr(1,[billtolname],",")+1,IIf(InStr(InStr(1,[billtolname],",")+1,[billtolname],",")=0,0,InStr(InStr(1,[billtolname],",")+1,[billtolname],",")-InStr(1,[billtolname],",")-1)))
'third
=IIf(InStr(InStr([billtolname],",")+1,[billtolname],",")<>0,Right([billtolname],Len([billtolname])-InStr(InStr([billtolname],",")+1,[billtolname],",")),Right([billtolname],Len([billtolname])-InStr([billtolname],",")))
It's fine if there are three names, but if the field only has one name, i get an #error on the first box.
Can anyone tell me how to build a better trap for this mouse?
Thank You