J
jhagan
Earlier this week, John Nurick helped with the answer pasted below.
I have tried everything to try to make this work, but when I get to the
part about the calculated field, I keep getting an error about an
ambiguos name in the field.
If anyone can help, I have uploaded a small version of the database
that can be downloaded from:
http:\\www.sar.org\temp\testtable.mdb
If anyone can get this to work, it can be emailed back to me at:
(e-mail address removed)
I am pretty much a novice at Access. This is a project for a
Non-Profit organization, so any help is greatly appreciated. Ideally,
there would be separate table for the ID with a record for each of the
six digit numbers which follows SAR# ?????? in the Notes4 field.
The data in this database was imported from a genealogy program, and
the only place to store the data in that program was in a notes field.
We are now trying to use the data for different purposes.
Jessie Hagan
NSSAR
Newsgroups: microsoft.public.access.queries
From: John Nurick <[email protected]> - Find messages by
this author
Date: Fri, 16 Dec 2005 08:05:31 +0000
Local: Fri, Dec 16 2005 3:05 am
Subject: Re: Extracting Partial Data from one field into another
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
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======================================
I have tried everything to try to make this work, but when I get to the
part about the calculated field, I keep getting an error about an
ambiguos name in the field.
If anyone can help, I have uploaded a small version of the database
that can be downloaded from:
http:\\www.sar.org\temp\testtable.mdb
If anyone can get this to work, it can be emailed back to me at:
(e-mail address removed)
I am pretty much a novice at Access. This is a project for a
Non-Profit organization, so any help is greatly appreciated. Ideally,
there would be separate table for the ID with a record for each of the
six digit numbers which follows SAR# ?????? in the Notes4 field.
The data in this database was imported from a genealogy program, and
the only place to store the data in that program was in a notes field.
We are now trying to use the data for different purposes.
Jessie Hagan
NSSAR
Newsgroups: microsoft.public.access.queries
From: John Nurick <[email protected]> - Find messages by
this author
Date: Fri, 16 Dec 2005 08:05:31 +0000
Local: Fri, Dec 16 2005 3:05 am
Subject: Re: Extracting Partial Data from one field into another
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
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======================================