Find (and remove) Specific Text in a field - for a Report 1

[bte]

I have data coming in a field as text. In some records, there is very specific text "..." and then more word text after it. (the QUOTES "" are not part of the text, just the ...)

I want to keep and report the beginning text in the field and I want to cut off the "..." and anything to the RIGHT of it.

How do I get Access to find the "..." and cut off the rest of the field ??

HELP?

P.S. - I cannot delete the bad data, just not display it on my report.

 

[SgtJarrow]

=Left([fieldname], InStr(1, [fieldname], "...&quot - 1)


should do the trick...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[bte]

Hey that worked! Thanks!

Unfortunately, not all records have the "..." in that field, so I was getting #ERROR when it did not...

So, I combined your code with a big IIF, checked to see if the InStr was returning Pos 0, blah blah, bada-bing - now it all works.

Here is my final code:

=IIf(InStr(1,[fieldname],"...&quot=0,[fieldname],Trim(Left([fieldname],InStr(1,[fieldname],"...&quot-1)))


THANK YOU!!!

 

[dhookom]

I think you could have kept it simple by using
=Left([fieldname] , InStr(1, [fieldname] & "...", "...&quot - 1)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SgtJarrow]

Nice Duane! I never would have thought of that.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[bte]

Sweeet. That looks more efficient than my big ole IIF statement.

The only drawback is that is seems to cut off a PERIOD at the end of my text (if there was a period). It is okay in this case, the final period is not crutial data.

Can you explain to me the Second Set of "..." in the InStr statement?

Is that a "Custom Compare Type" or something?


Thanks again, both of you helped me!

 

[dhookom]

I think the expression provided would have failed if the field didn't contain the "..." so I created an expression that would always contain "...". For instance if the field value was "Green" then
=Left([fieldname] , InStr(1, [fieldname] , "...") - 1)
would fail since Instr() would return 0 and subtracting 1 would cause an issue.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.