Re: Compensating for Bad Data

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Johnny Polite" <JohnnyPolite@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:62B1B2AD-E546-4FE9-80C1-6E8533C38873@xxxxxxxxxxxxxxxx
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


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


.

Relevant Pages

  • RE: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: VBA Function Exits Unexpectedly
    ... >> Dim month As String, ... >> Dim rstagents As Recordset ... > Set rstAgents = New ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: macro error due to editor
    ... Your VBA code never sets DB or the Recordset variables to Nothing. ... Do you declare DB as a global variable anywhere in the database file (e.g., ... EmpDateAs String ... Dim DB As Database, Qry As QueryDef, Qry_def As String ...
    (microsoft.public.access.macros)
  • Re: Edit/Update results in badly fragmented file
    ... Microsoft Jet objects when using DAO in *other* Microsoft Office programs. ... While the article discusses opening a recordset using DAO, ... Public Function ScrubValue (strMPN As String) As String ... Dim blnHasNum As Boolean ...
    (microsoft.public.access.modulesdaovba)