Compensating for Bad Data

[Johnny Polite]

Hello all,

I am using a piece of data collection software that has proven to be quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the semicolon-space and
determine if they are equal, then strip one side or the other to return the
intended "Value".

My mind is almost there. I already have a piece of code that loops through
all the tables and replaces the value "[No Answer Entered] with a Null value.
I am having trouble figuring out how to catch the semicolon-space then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and helpful.
What an impressive resource this is.

Take care,

JP

 

[Dale Fye]

Johnny,

What do you want to do if the values on either side of the semicolon are not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search it.
For intField = 1 to rs.fields.count-1 'think this is zero based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the left and
'right side of the ';'
'the debug will print the PK and the field name where the
'problem exists, but the debug window only contains about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale

 

[Johnny Polite]

Hey Dale,

First, thanks for your help. I was thinking the intstr was the way to go.
I got to sort out how to do the rest. I think I can get it though.

Answers to the questions -

1. I need to leave the value alone if the two sides of the semicolon are
not the same. That just means they chose to use a semicolon in their answer.

2. This happens for both text and numerical values.

Here's what I am thinking after I flag a string that meets the instr test.
After that, I can count the characters to the left of the semicolon and to
the right. Then I can assign a variable to the front of the value by
stripping off the end of the value. I will repeat the process for the end of
the value and assign that to a different variable. Then, if the two equal
each other, I will just make the field equal one of the variables.

Does that sound about right? I am a novice, so I have to research the exact
syntax.

Thanks again for your help!

-JP

Johnny,

What do you want to do if the values on either side of the semicolon are not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search it.
For intField = 1 to rs.fields.count-1 'think this is zero based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the left and
'right side of the ';'
'the debug will print the PK and the field name where the
'problem exists, but the debug window only contains about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello all,

I am using a piece of data collection software that has proven to be quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the semicolon-space and
determine if they are equal, then strip one side or the other to return the
intended "Value".

My mind is almost there. I already have a piece of code that loops through
all the tables and replaces the value "[No Answer Entered] with a Null value.
I am having trouble figuring out how to catch the semicolon-space then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and helpful.
What an impressive resource this is.

Take care,

JP

 

[wphx]

dim v1 as variant
dim v2 as variant
dim p as integer

p=instr(r!data,"; ")
v1=trim(left(r!data,p-1)
v2=trim(mid(r!data,p+2)

if v1=v2 then
...
else
...
end if

... or something like that

else you might be able to use the split() function - which will split a
string into an array using a given delimiter
'

another possibility is to add an extra yes/no field to your record, and
instead of altering the data, simply set the flag to yes/no or true/false
depending on the match. That way you could highlight possibly brummy data or
even filter by it.

Here's an sql statement you could put into a query. Its based on a table
called test where the field is called 'data' and 'isGlitch' is a yes/no
field with a default value of 'no'

UPDATE test SET test.isGlitch = True
WHERE (((InStr([data],"; "))=True) AND ((Trim(Left([data],InStr([data],";
")-1)))=Trim(Mid([data],InStr([data],"; ")+2))));

You can then run the query and filter the input table by the isGlitch field

NB: I did this quickly - not tested - might be an error but you get the idea

cheers

Hey Dale,

First, thanks for your help. I was thinking the intstr was the way to go.
I got to sort out how to do the rest. I think I can get it though.

Answers to the questions -

1. I need to leave the value alone if the two sides of the semicolon are
not the same. That just means they chose to use a semicolon in their
answer.

2. This happens for both text and numerical values.

Here's what I am thinking after I flag a string that meets the instr test.
After that, I can count the characters to the left of the semicolon and to
the right. Then I can assign a variable to the front of the value by
stripping off the end of the value. I will repeat the process for the end
of
the value and assign that to a different variable. Then, if the two equal
each other, I will just make the field equal one of the variables.

Does that sound about right? I am a novice, so I have to research the
exact
syntax.

Thanks again for your help!

-JP

Johnny,

What do you want to do if the values on either side of the semicolon are
not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search it.
For intField = 1 to rs.fields.count-1 'think this is zero based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the left
and
'right side of the ';'
'the debug will print the PK and the field name where the
'problem exists, but the debug window only contains about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello all,

I am using a piece of data collection software that has proven to be
quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the semicolon-space
and
determine if they are equal, then strip one side or the other to return
the
intended "Value".

My mind is almost there. I already have a piece of code that loops
through
all the tables and replaces the value "[No Answer Entered] with a Null
value.
I am having trouble figuring out how to catch the semicolon-space then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and
helpful.
What an impressive resource this is.

Take care,

JP

 

[Johnny Polite]

Okay...thanks so much guys for your help. I am confident that I am one line
away from getting this right. I have all the string manipulation down. I am
just messing up on my syntax for changing the value of a field in a recordset
to a variable.

Here's my code:

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim tblName as String
Dim fld As DAO.Field
Dim fldName As String


Set db = CurrentDb
For Each tdf In db.TableDefs

TblName = tdf.Name
If Left(TblName, 4) <> "MSys" Then
Set rs = db.OpenRecordset(TblName)
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
For Each fld In rs.Fields
fld.Name = fldName
intSemi = InStr(fld.Value, "; ")
If intSemi <> 0 Then
LeftValue = Left(fld.Value, intSemi - 1)
RightValue = Right(fld.Value, intSemi - 1)
If LeftValue = RightValue Then
rs.fld(fldName) = LeftValue <-- THIS IS THE PROBLEM
End If
End If
Next fld
rs.MoveNext
Wend
rs.Close
End If
End If
Next

Set tdf = Nothing
Set db = Nothing


End Sub

I keep getting a "Method or Data Member Not Found" compile error. I need to
walk away from it. There is a dent in my monitor in the shape of my forehead.

Thanks again in advance for the help. I hope to find an enlightening
solution when I awake tomorrow.

Take care.

-JP

dim v1 as variant
dim v2 as variant
dim p as integer

p=instr(r!data,"; ")
v1=trim(left(r!data,p-1)
v2=trim(mid(r!data,p+2)

if v1=v2 then
...
else
...
end if

... or something like that

else you might be able to use the split() function - which will split a
string into an array using a given delimiter
'

another possibility is to add an extra yes/no field to your record, and
instead of altering the data, simply set the flag to yes/no or true/false
depending on the match. That way you could highlight possibly brummy data or
even filter by it.

Here's an sql statement you could put into a query. Its based on a table
called test where the field is called 'data' and 'isGlitch' is a yes/no
field with a default value of 'no'

UPDATE test SET test.isGlitch = True
WHERE (((InStr([data],"; "))=True) AND ((Trim(Left([data],InStr([data],";
")-1)))=Trim(Mid([data],InStr([data],"; ")+2))));

You can then run the query and filter the input table by the isGlitch field

NB: I did this quickly - not tested - might be an error but you get the idea

cheers

Hey Dale,

First, thanks for your help. I was thinking the intstr was the way to go.
I got to sort out how to do the rest. I think I can get it though.

Answers to the questions -

1. I need to leave the value alone if the two sides of the semicolon are
not the same. That just means they chose to use a semicolon in their
answer.

2. This happens for both text and numerical values.

Here's what I am thinking after I flag a string that meets the instr test.
After that, I can count the characters to the left of the semicolon and to
the right. Then I can assign a variable to the front of the value by
stripping off the end of the value. I will repeat the process for the end
of
the value and assign that to a different variable. Then, if the two equal
each other, I will just make the field equal one of the variables.

Does that sound about right? I am a novice, so I have to research the
exact
syntax.

Thanks again for your help!

-JP

Johnny,

What do you want to do if the values on either side of the semicolon are
not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search it.
For intField = 1 to rs.fields.count-1 'think this is zero based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the left
and
'right side of the ';'
'the debug will print the PK and the field name where the
'problem exists, but the debug window only contains about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hello all,

I am using a piece of data collection software that has proven to be
quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the semicolon-space
and
determine if they are equal, then strip one side or the other to return
the
intended "Value".

My mind is almost there. I already have a piece of code that loops
through
all the tables and replaces the value "[No Answer Entered] with a Null
value.
I am having trouble figuring out how to catch the semicolon-space then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and
helpful.
What an impressive resource this is.

Take care,

JP

 

[wphx]

Okay...thanks so much guys for your help. I am confident that I am one
line
away from getting this right. I have all the string manipulation down. I
am
just messing up on my syntax for changing the value of a field in a
recordset
to a variable.

Here's my code:

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim tblName as String
Dim fld As DAO.Field
Dim fldName As String


Set db = CurrentDb
For Each tdf In db.TableDefs

TblName = tdf.Name
If Left(TblName, 4) <> "MSys" Then
Set rs = db.OpenRecordset(TblName)
If rs.RecordCount > 0 Then

*** if I remember rightly, for a dao recordset, to get a true recordcount
you need to do a rs.movelast first. But if there are no records in the table
this will return an error. Look at using rs.EOF or rs.BOF to determine an
empty table

rs.MoveFirst
While Not rs.EOF
For Each fld In rs.Fields
fld.Name = fldName

**** fldName=fld.Name

intSemi = InStr(fld.Value, "; ")
If intSemi <> 0 Then
LeftValue = Left(fld.Value, intSemi - 1)
RightValue = Right(fld.Value, intSemi - 1)

*** ummm -- not sure about this ^^^ intSemi is the position of the ';'
right() returns intSemi characters counting backwards from the right end of
the string - using the mid() function will return all characters beginning
at intSemi to the end of the string. With the right function you won't be
truly parsing the two values, butI guess if you are only interested in the
values which are identical you could get away with it (assuming no trailing
spaces) but it feels a it dodgy.


i.e. "abcde;fgh"
intSemi=6
left("abcde;fgh",intSemi-1) returns "abcde" (OK)
right("abcde;fgh",intSemi-1) returns "e;fgh" (Dodgy, but will work only
where leftvalue & rightvalue are equal)
mid("abcde;fgh",intSemi+1) returns "fgh" (OK)

If LeftValue = RightValue Then
rs.fld(fldName) = LeftValue <-- THIS IS THE PROBLEM

*** off the top of my head, try

rs.edit
rs.Fields(fldName)=leftvalue
rs.update

 

[wphx]

in addition to my previous post: this could be better

While Not rs.EOF
For Each fld In rs.Fields
rs.edit

fld.Name = fldName
intSemi = InStr(fld.Value, "; ")
If intSemi <> 0 Then
LeftValue = Left(fld.Value, intSemi - 1)
RightValue = Right(fld.Value, intSemi - 1)
If LeftValue = RightValue Then
rs.fld(fldName) = LeftValue <-- THIS IS THE PROBLEM

should really be:

rs.Fields(fldName)=LeftValue

you might get away with (but test it first):

fld=LeftValue

or possibly

fld.value=LeftValue

End If
End If
Next fld

Next (I don't think fld is needed here)

rs.update

rs.MoveNext
Wend
rs.Close
End If
End If
Next
set rs=nothing
Set tdf = Nothing
Set db = Nothing


End Sub

I keep getting a "Method or Data Member Not Found" compile error. I need
to
walk away from it. There is a dent in my monitor in the shape of my
forehead.

Thanks again in advance for the help. I hope to find an enlightening
solution when I awake tomorrow.

Take care.

-JP

dim v1 as variant
dim v2 as variant
dim p as integer

p=instr(r!data,"; ")
v1=trim(left(r!data,p-1)
v2=trim(mid(r!data,p+2)

if v1=v2 then
...
else
...
end if

... or something like that

else you might be able to use the split() function - which will split a
string into an array using a given delimiter
'

another possibility is to add an extra yes/no field to your record, and
instead of altering the data, simply set the flag to yes/no or true/false
depending on the match. That way you could highlight possibly brummy data
or
even filter by it.

Here's an sql statement you could put into a query. Its based on a table
called test where the field is called 'data' and 'isGlitch' is a yes/no
field with a default value of 'no'

UPDATE test SET test.isGlitch = True
WHERE (((InStr([data],"; "))=True) AND ((Trim(Left([data],InStr([data],";
")-1)))=Trim(Mid([data],InStr([data],"; ")+2))));

You can then run the query and filter the input table by the isGlitch
field

NB: I did this quickly - not tested - might be an error but you get the
idea

cheers

Hey Dale,

First, thanks for your help. I was thinking the intstr was the way to
go.
I got to sort out how to do the rest. I think I can get it though.

Answers to the questions -

1. I need to leave the value alone if the two sides of the semicolon
are
not the same. That just means they chose to use a semicolon in their
answer.

2. This happens for both text and numerical values.

Here's what I am thinking after I flag a string that meets the instr
test.
After that, I can count the characters to the left of the semicolon and
to
the right. Then I can assign a variable to the front of the value by
stripping off the end of the value. I will repeat the process for the
end
of
the value and assign that to a different variable. Then, if the two
equal
each other, I will just make the field equal one of the variables.

Does that sound about right? I am a novice, so I have to research the
exact
syntax.

Thanks again for your help!

-JP

:

Johnny,

What do you want to do if the values on either side of the semicolon
are
not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search
it.
For intField = 1 to rs.fields.count-1 'think this is zero
based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the
left
and
'right side of the ';'
'the debug will print the PK and the field name where
the
'problem exists, but the debug window only contains
about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hello all,

I am using a piece of data collection software that has proven to be
quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the
semicolon-space
and
determine if they are equal, then strip one side or the other to
return
the
intended "Value".

My mind is almost there. I already have a piece of code that loops
through
all the tables and replaces the value "[No Answer Entered] with a
Null
value.
I am having trouble figuring out how to catch the semicolon-space
then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and
helpful.
What an impressive resource this is.

Take care,

JP

 

[Johnny Polite]

Hey,

Thank you! I actually got it to work before I read your post with some
advice from a buddy of mine. It's nice to see some independent verification,
though. And I ran across some scenarios (i.e., in the unlikely event that a
user started an answer with a "; ") where my string manipulation was flawed.
I used the Mid( ) function and that seemed to take care of it. And I am only
interested in alike values. It is a byproduct of a glitch created by premade
software that I am using to collect data.

Man, I appreciate your help. Very kind of you to guide me along. I just
started doing VBA about a month ago to try and knock out this project for
work. I was tired of seeing us operate in the dark ages using reams and
reams of paper.

Again, thanks!

-JP

in addition to my previous post: this could be better

While Not rs.EOF
For Each fld In rs.Fields
rs.edit

fld.Name = fldName
intSemi = InStr(fld.Value, "; ")
If intSemi <> 0 Then
LeftValue = Left(fld.Value, intSemi - 1)
RightValue = Right(fld.Value, intSemi - 1)
If LeftValue = RightValue Then
rs.fld(fldName) = LeftValue <-- THIS IS THE PROBLEM

should really be:

rs.Fields(fldName)=LeftValue

you might get away with (but test it first):

fld=LeftValue

or possibly

fld.value=LeftValue

End If
End If
Next fld

Next (I don't think fld is needed here)

rs.update

rs.MoveNext
Wend
rs.Close
End If
End If
Next
set rs=nothing
Set tdf = Nothing
Set db = Nothing


End Sub

I keep getting a "Method or Data Member Not Found" compile error. I need
to
walk away from it. There is a dent in my monitor in the shape of my
forehead.

Thanks again in advance for the help. I hope to find an enlightening
solution when I awake tomorrow.

Take care.

-JP

dim v1 as variant
dim v2 as variant
dim p as integer

p=instr(r!data,"; ")
v1=trim(left(r!data,p-1)
v2=trim(mid(r!data,p+2)

if v1=v2 then
...
else
...
end if

... or something like that

else you might be able to use the split() function - which will split a
string into an array using a given delimiter
'

another possibility is to add an extra yes/no field to your record, and
instead of altering the data, simply set the flag to yes/no or true/false
depending on the match. That way you could highlight possibly brummy data
or
even filter by it.

Here's an sql statement you could put into a query. Its based on a table
called test where the field is called 'data' and 'isGlitch' is a yes/no
field with a default value of 'no'

UPDATE test SET test.isGlitch = True
WHERE (((InStr([data],"; "))=True) AND ((Trim(Left([data],InStr([data],";
")-1)))=Trim(Mid([data],InStr([data],"; ")+2))));

You can then run the query and filter the input table by the isGlitch
field

NB: I did this quickly - not tested - might be an error but you get the
idea

cheers


Hey Dale,

First, thanks for your help. I was thinking the intstr was the way to
go.
I got to sort out how to do the rest. I think I can get it though.

Answers to the questions -

1. I need to leave the value alone if the two sides of the semicolon
are
not the same. That just means they chose to use a semicolon in their
answer.

2. This happens for both text and numerical values.

Here's what I am thinking after I flag a string that meets the instr
test.
After that, I can count the characters to the left of the semicolon and
to
the right. Then I can assign a variable to the front of the value by
stripping off the end of the value. I will repeat the process for the
end
of
the value and assign that to a different variable. Then, if the two
equal
each other, I will just make the field equal one of the variables.

Does that sound about right? I am a novice, so I have to research the
exact
syntax.

Thanks again for your help!

-JP

:

Johnny,

What do you want to do if the values on either side of the semicolon
are
not
the same?

Are the "values" all numeric, or are the text, or both?

You might try something like:

Private Sub SearchForSemi()

Dim strsql as string
Dim rs as DAO.Recordset
Dim intField as integer

strSQL = "SELECT * FROM yourTable "
set rs = currentdb.openrecordset(strsql)

While not rs.eof
'assumes that field(0) is the PK, so you don't want to search
it.
For intField = 1 to rs.fields.count-1 'think this is zero
based
if instr(rs(intField), "; ") <> 0 THEN
'put some code in here to compare the values on the
left
and
'right side of the ';'
'the debug will print the PK and the field name where
the
'problem exists, but the debug window only contains
about
250 lines
debug.print rs(0), rs(intField).Name
End if
Next
rs.movenext
Wend
rs.close
set rs = nothing
end sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hello all,

I am using a piece of data collection software that has proven to be
quite
glitchy. My major problem is that at random times it will return an
identical value twice separated by a semicolon then a space.

Ex: "Value; Value"

I need to loop through the fields in all the tables, spot the
semicolon-space, compare the value before and after the
semicolon-space
and
determine if they are equal, then strip one side or the other to
return
the
intended "Value".

My mind is almost there. I already have a piece of code that loops
through
all the tables and replaces the value "[No Answer Entered] with a
Null
value.
I am having trouble figuring out how to catch the semicolon-space
then
compare the two sides of the value.

I appreciate any help in advance. You guys are always so great and
helpful.
What an impressive resource this is.

Take care,

JP

 

[gllincoln]

Two choices I can think of, off the top of my balding head - one would be to
create a user defined type that represents a row worth of entries. It's a
pain to build but it's elegant to use - you can step through with for each
or address the elements by their member name, do virtually anything with the
data, makes the code cool and rather nicely self-documenting.

But I'm too lazy for that so I would create a variant array (variant so it
can handle whatever data type I catch hopefully) large enough to insert a
row's worth of data, then do whatever I needed to do with that - clean it up
and then insert it into the database.

(leaving out hooking the housekeeping chore of hooking rs up as an object
reference to the recordset - I'm assuming that rs = your table and the row
of data you wish to interrogate is already in the table.) This is untested
psuedo code, probably have a typo or some other syntactical error - but
should be easily fixed, the logic will work.

If you are doing this before inserting the new row - then you wouldn't use
rs.eof to walk through the rows - rs.edit should be changed to rs.addnew and
you need to make really sure that your incoming data has the exact same
shape and size and order at this point, so your Access table receives what
it is expecting to recieve.

If you have an autonumber pimary key as the first field of your access table
then start the Edit or Update x loop with a value of 1 instead of 0.

dim myArray(50) as Variant ' or whatever size you need
dim f as field, i as integer, x as integer
i=0
do while not rs.eof
for each f in rs.fields
myArray(i)= rs.fields(i)
i=i+1
next f
rs.edit
for x = 0 to i
rs.fields(x) = iif(instr(myArray(x),"; ")>0, _
trim(left(myArray(x),instr(myArray(x), "; ")-1)), myArray(x))
next x
rs.update
loop

Hope this helps.

Gordon