Re: Compensating for Bad Data

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


"wphx" wrote:

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


"Johnny Polite" <JohnnyPolite@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ADBD39DD-7D90-4E86-A8AC-5A2AAE58E589@xxxxxxxxxxxxxxxx
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

"Dale Fye" wrote:

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.



"Johnny Polite" wrote:

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



.

Relevant Pages

  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Help answer these 70-310 questions
    ... One argument is the string ... Dim output As New StringBuilder ... EmployeeLocations. ... You create a strongly named serviced component. ...
    (microsoft.public.cert.exam.mcad)
  • Help answer these 70-310 questions
    ... One argument is the string ... Dim output As New StringBuilder ... EmployeeLocations. ... You create a strongly named serviced component. ...
    (microsoft.public.cert.exam.mcsd)