Solved

Find a sting in a field

Posted on 2013-11-12
11
344 Views
Last Modified: 2013-11-13
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
Comment
Question by:shieldsco
  • 6
  • 4
11 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:shieldsco
Comment Utility
Capricorn1-

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

xStr = Split(vArr(1), ":")(1)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
like i said 'this may vary depending on the content of the memo field "

can you upload a sample db with the table?
0
 

Author Comment

by:shieldsco
Comment Utility
Find attached sample DB
Database8.accdb
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:shieldsco
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:shieldsco
Comment Utility
disregard
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 

Author Comment

by:shieldsco
Comment Utility
I'm unable to download from my work computer - so what are the chages to the query and function -- thanks
0
 

Author Closing Comment

by:shieldsco
Comment Utility
Thanks - very good
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now