Find a sting in a field

I have a text field in table that I would like find any records that contain 0512 and then write the results to a seperate text field.
Original Transaction Type: C;Original Document Reference Number: 05128572;Original Accomplished Date: 2013-07-23;Adjuster DO Symbol: X0051;Original Accounting Date: 2013-07-31;***CHARGEBACK** SUPPORTING DOCUMENTS DIDN'T CONTAIN THE REQUIRED BILLING INFO. POC FOR AGENCIES: CDC-CURTIS JUE IGY9@CDC.GOV ; MCC-GUSTAVO MARRUFO  GMARRUFO@IBC.DOI.GOV

In the above example 05128572 would be written to a seperate text field.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this, run your query and look for  columns Expr3, Expr2

you have to change your criteria
from  "*0512*"

to   "* 0512*"     ' add a space after the first *
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You'll want to use InStr(), which will return the position of a string found within another string.

If Instr(strToSearch, "0512")>0 then

You can then use Mid$() to return some part of that string pased:

  strResult = Mid$(Instr(strToSearch, "0512"),10)

You can define query columns using the above as well

Rey Obrero (Capricorn1)Commented:
you will need a vba function to do that, place this function in a regual module

Function getVarString(vMemo)
Dim s As String, vArr() As String, xStr As String
s = vMemo
if instr(s,"0512") then
vArr = Split(s, ";")
xStr = Split(vArr(1), ":")(1)
getVarString = xStr

getVarString = ""

end if
End Function

then create a query like this

select id, getVarString([MemoField]) from tableName

this may vary depending on the content of the memo field
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

shieldscoAuthor Commented:

I get the following error : Runtime 9 -- subscript out of range

xStr = Split(vArr(1), ":")(1)
Rey Obrero (Capricorn1)Commented:
like i said 'this may vary depending on the content of the memo field "

can you upload a sample db with the table?
shieldscoAuthor Commented:
Find attached sample DB
shieldscoAuthor Commented:
A memo can also look like :

1. Accidentally sent invoice through on collection 05129563 without FSN
2. Reversal of Doc Ref Num: 05129859. FSN missing. Backup available upon request
Rey Obrero (Capricorn1)Commented:
how do you want to treat this memo

LOA:11FED1110195 75090421 75-X-0512 25102 927645465 939 ZWYG  2004211101 VFC 11FED1110195

this is one of record causing the search to fail.
shieldscoAuthor Commented:
shieldscoAuthor Commented:
I'm unable to download from my work computer - so what are the chages to the query and function -- thanks
shieldscoAuthor Commented:
Thanks - very good
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.