Re: Compensating for Bad Data

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

"wphx" wrote:

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


"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

  • Re: =?UTF-8?B?UHJvYmxlbSByZXR1cm5pbmcgaWRlbnRpdHkgZnJvbSBTUUwgU2Vy?= =?UTF-8?B?dmVyIHdoZW4gc
    ... It worked fine until I typed a semicolon into one of the string fields to be inserted. ... The string fields are inside single quotes in the INSERT command. ... With the semicolon in the string, the record is written correctly including the semicolon, but the identity is not returned. ... Dim MyCommand As SqlCommand = New SqlCommand ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Compensating for Bad Data
    ... I have all the string manipulation down. ... Dim rs As DAO.Recordset ... I need to leave the value alone if the two sides of the semicolon are ... compare the value before and after the semicolon-space ...
    (microsoft.public.access.externaldata)
  • Re: Compensating for Bad Data
    ... dim v1 as variant ... I need to leave the value alone if the two sides of the semicolon are ... compare the value before and after the semicolon-space ...
    (microsoft.public.access.externaldata)
  • Need Help With Export Code
    ... Below is some code I wish to use to export a "modified" csv file. ... By "modified" I mean that the csv file uses a semicolon; ... Dim FName As String ... Set ftext = fs.CreateTextFile ...
    (microsoft.public.excel.programming)
  • Re: Compensating for Bad Data
    ... For Each fld In rs.Fields ... If intSemi 0 Then ... dim v2 as variant ... I need to leave the value alone if the two sides of the semicolon ...
    (microsoft.public.access.externaldata)