Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

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.
Example:
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.
0
shieldsco
Asked:
shieldsco
  • 6
  • 4
1 Solution
 
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

Jim.
0
 
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

else
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
0
 
shieldscoAuthor Commented:
Capricorn1-

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

xStr = Split(vArr(1), ":")(1)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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?
0
 
shieldscoAuthor Commented:
Find attached sample DB
Database8.accdb
0
 
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
0
 
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.
0
 
shieldscoAuthor Commented:
disregard
0
 
Rey Obrero (Capricorn1)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 *
Database8.accdb
0
 
shieldscoAuthor Commented:
I'm unable to download from my work computer - so what are the chages to the query and function -- thanks
0
 
shieldscoAuthor Commented:
Thanks - very good
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now