Remove Left 3 characters if they match criteria

  • Thread starter Thread starter Susan
  • Start date Start date
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!!
 
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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Susan

You need to switch your expressions around so the semicolon only appears *after* a populated
string:-

txtCommentCombine: IIf(IsNull([Comment]),"",[Comment]&"; ") &
IIf(IsNull([Comment2]),"",[Comment2]&"; ")& IIf(IsNull([Comment3]),"",[Comment3])

This way, you won't get an initial semicolon.

HTH
Andrew L.
 
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!!
 
Back
Top