Remove Left 3 characters if they match criteria

S

Susan

I thought I had done something like this before, but
can't seem to find it or remember how.

I am appending data from 10 fields into one using a
query. The data will be separated by a "; " (semi-colan
and 2 spaces) If there is no data in the first field, my
final answer begins with this string "; " and I want to
somehow evaluate the answer and if it begins with this, I
want to move 3 characters to the right and only append
from there to the end of the remaining string.

My first field is:
txtCommentCombine: IIf([Comment] Is Not Null,
[Comment],"") & IIf([Comment2] Is Not Null,"; " &
[Comment2],& IIf([Comment3] Is Not Null,"; " &
[Comment3]"")

Then I wanted my second field to look at the first 3
characters of the above [txtCommentCombine] and if it
is "; " I either need to remove those 3 or just go past
them.....any ideas? Am I going about this all wrong??

THANKS!!
 
R

RobFMS

Susan

Although I do not have a working example to show you, are you familar with
the Split() and Join() functions? The idea you have using the semi-colon as
a delimiter is good. Take a look in the help files for more detail and see
if these functions give you a bit more flexibility for what you want to do.

Let us know if it helps.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
G

Guest

Thanks for the help!!
I used this to combine the fields:
txtCommentCombine: IIf([Comment] Is Not Null,
[Comment],"") & IIf([Comment2] Is Not Null,"; " &
[Comment2],"") & IIf([Comment3] Is Not Null,"; " &
[Comment3],"") & IIf([Comment4] Is Not Null,"; " &
[Comment4],"") & IIf([Comment5] Is Not Null,"; " &
[Comment5],"") & IIf([Comment6] Is Not Null,"; " &
[Comment6],"") & IIf([Comment7] Is Not Null,"; " &
[Comment7],"") & IIf([Comment8] Is Not Null,"; " &
[Comment8],"") & IIf([Comment9] Is Not Null,"; " &
[Comment9],"") & IIf([Comment10] Is Not Null,"; " &
[Comment10],"")

Then I used this to only use what was to the right of
the "; " if that is what the field started with:
txtComment: IIf([txtCommentCombine] Not Like "; *",
[txtCommentCombine],Right$([txtCommentCombine],Len
([txtCommentCombine])-InStr(1,[txtCommentCombine],"; ")-
2))

I couldn't find any help on the Join() function.
THANKS AGAIN!!
 
Top