Hi Jessie,
The multiple "SAR" values in the Notes4 field probably mean you have a
1:many relationship between (1) whatever entity is represented by a
record in your table and (many) the "SAR" values. The correct way to
handle this in a relational database such as Access is not to create
duplicate records in your existing table, but to add a second, related
table.
This table probably needs just two columns: one for the primary key
value from the main table, and one for the SAR number. For the moment,
I'll assume that your existing table is called tblMain, and that its
primary key is the field ID.
Before you start, make certain that you have an up to date backup copy
of your database.
Create a new table. Let's call it tblSARs, with fields
ID (data type the same as the primary key of tblMain unless that's
an Autonumber, in which case this one needs to be Number-Long).
SAR (text, 12 characters).
While you're still in Table Design view, select both these fields and
then go to the Edit menu and select Primary Key, thus including _both_
fields in the table's primary key.
Next, open the Relationships window. Display the two tables, and drag
the ID (primary key) field from tblMain onto the corresponding field in
tblSARs. Create the relationship, setting it to display all records from
tblMain and only matching records from tblSARs, and to enforce
relational integrity.
Now you need to run a series of append queries to create one record in
tblSARs for each SAR value in each record in tblMain.
The VBA function below will help: it extracts one SAR value at a time
from the string in the Notes4 field. Paste it into a new code module in
your database, and save the module (naming it something other than
ParseSAR).
To use it, create an append query that gets its data from tblMain and
appends to tblSARS.
The query needs just two fields. For the first, select ID (or whatever
the tblMain primary key is called) and append it to the corresponding
field in tblSars.
The second is a calculated field, using an expression like this:
SAR: ParseSAR([Notes4], 0)
Set it to append to the SAR field in tblSars. In the Criteria row, put
Is Not Null
When you run the query, it will pass the contents of Notes4 to the
function, which will return the first SAR number in the field for the
query to append to tblSARs.
Next, modify the query so the calculated field is
SAR: ParseSAR([Notes4], 1)
and run it again. Changing the 0 to 1 makes the function return the
second SAR number in the string.
Carry on as many times as necessary. If there are up to 8 SAR numbers in
Notes4, you neeed to run the query with numbers from 0 to 7.
'Start of VBA code=====================================
Public Function ParseSAR(V As Variant, Item As Long) As Variant
'extracts substrings like
' SAR #123456
'from a string.
'The Item argument determines which substring is returned:
' Item 0 is the first, Item 1 the second, etc.
' Returns Null if there aren't enough matching substrings
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim Result As String
ParseSAR = Null 'default value
If IsNull(V) Then
Exit Function
End If
Set oRE = CreateObject("Vbscript.Regexp")
With oRE
.Pattern = "SAR #\d+"
.Global = True
.IgnoreCase = True
Set oMatches = .Execute(CStr(V))
End With
If Item < oMatches.Count Then
ParseSAR = oMatches(Item)
End If
End Function
'End of code======================================
have a text field with a lot of different data in it.
Here is an example:
SERV: PS NJ////SAR #155097//OH #08468////SAR #158097//OH #08469////SAR
#158098//OH #08470////SAR #158099//OH #08471////SAR #158100//OH #08472
The name of the field where this data is stored in Notes4. I want to
put the data into a field called Numbers
I want to be able to extract out the Member numbers which are the
characters
"SAR #??????"
Ideally It would duplicate the record and put each different number
into each duplicated record, but I wouldn't mind if pulled all five
instances into one field. I can seperate them manually later.
Sometimes, there is only one instance and other times, there can be up
to 8 in the field
So far, I have tried an update query with [Notes4] Like "SAR #???????"
This just put zeros in the field.
I am fairly new, so if anyone can help in as plain english as possible,
it would be greatly appreciated.
Thanks
JessieH
