Not sure if this is possible

[buckeyejerry]

I have a database with about 2100 records. In the table is a memo field containing a description of the job. And in that is a case number (like this 2012-red-01-1234). If possible I would like a way to pull that case number out of the memo field and put it into a separate field. Here is an example of what would be in the memo field. (TRANSCRIPT IN THE MATTER OF John Smith V. Some other BOARD, CASE #2011-REM-04-1234, HEARD ON 10-2-01. 165 PAGES @$1.25 PER PAGE.

Any help would be greatly appreciated. BTW I am fairly new at using Access.



Thanks.....

[RuralGuy]

Is the Case # *always* in that same format? "#" preceding the value and followed by a "," comma?

[buckeyejerry]

Unfortunately no. I would say about 80% are.

[buckeyejerry]

I don't want to waste anyone's time on this as it is not absolutely necessary I accomplish this. It would just make things easier for my wife. I just thought there may be a easy formula to do this.

[RuralGuy]

Is there anything consistant in the way the value appears in the memo field? Computers are really dumb you know.

[buckeyejerry]

If there is a way to get it to work just using the #2011-REM-04-1234 that would be a big help. I could manually enter the rest of them.

[RuralGuy]

There is! Are there any other "#" characters in the memo field? Are all of the values 16 characters long? Is the value always in the form "9999-AAA-99-9999"?

[RuralGuy]

Here's a function for a Standard Module that could be used in an Update query to populate the *new* CaseNum field.

Public Function CaseNum(InField As String) As String
' Extract the Case Number from the incoming InField value, if it exists.   Dim StartPos As Long
   StartPos = Nz(InStr(InField, "#"), 0)
   If StartPos > 0 Then
      CaseNum = Mid(InField, StartPos + 1, 16)
   Else
      CaseNum = ""
   End If
End Function

You would first need to create this new field in the table as a String DataType (Text) and then you could run the query to populate the field. The function currently assumes the value is the first 16 characters after the first "#" character in the memo field. Be sure and try this on a backUp of your db before going for broke.

[June7]

If the # character is not reliable but the hyphenation structure and 16 character length are reliable, then the function could locate position of first hyphen and and subtract 4 for the beginning of the case number; however, this assumes hyphens are not in the string preceding the case number.

[buckeyejerry]

Thanks for the help guys. But I believe this is all a bit to much for me to grasp right now. I think maybe its time to buy some books and start reading. I think the best route now is to enter them manually. I can't seem to figure ut wher to put the expression to get it to work right.

[June7]

The public function would be placed in a VBA general module. Open the VBA editor, insert Module, type code.

Then build an UPDATE query. In the Update To row under the CaseNumber field, call the function with:

CaseNum([memo field name here])

Access Help has guidance on creating UPDATE query.