Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and replace from table using VBA (2003)

    Hi,

    I have a table with a field called visittime, its a download from another system. the field will have approx 4 times in side each record seperated with I have a module which uses this as the seperater to split the record into its four seprate events and append to a master table.

    Occasionally the other system will throw out a set of times with instead of

    In VBA how do I use a find and replace idea to search the table (called tblPASdata) for and replace with

    example data in one field

    09:5210:1510:3311:01 Notice the double between 10:15 and 10:33

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and replace from table using VBA (2003)

    Which application? Access, Excel, Word?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace from table using VBA (2003)

    Sorry, that would help.

    Access
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and replace from table using VBA (2003)

    I have moved this thread to the Access forum. Please post all questions about Access here in the future; thank you.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and replace from table using VBA (2003)

    You write that you already have a module that splits the field on . You could ignore all empty strings that result from the split in this code.

    In your example, splitting on would result in "09:52", "10:15", "" (empty string), "10:33" and "11:01". You can weed out the empty string by using code such as

    ' Code to get n-th part
    strPart = ...
    If Not strPart = "" Then
    ...
    End If

  6. #6
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace from table using VBA (2003)

    I'm not sure how I understand how to make this work,

    In my example I do get an empty string however, there is another field that describes what each stage was I need to match that to the time. For example
    VisitStage: VisitTime:
    ADM 09:52
    TRI 10:15
    SHO Empty String
    DIS 10:33
    11:01

    You see how every thing is out of place, 10:33 should be next to SHO and 11.01 next to DIS, further down the code that splits the field on I set the field in the desitnation i.e:
    rec!VisitTime = Canc(8, x)
    Canc(8,x) in the case of is Null Then I get a data mismatch because rec!VisitTime is a Date/Time field that cant be blank.

    Since my first post I have also noticed that some users type 24:00 in the PAS system instead of 00:00 unfortunatly the source data is recorded on an old DBase system that we cant ammend, it's a free text box! (almost)

    So I need to find some VBA code to clean the source data before its import so I can remove one of the and change 24:00 to 00:00

    I have attached the section of code that splits the fields and imports the data incase it helps.

    Thanks
    Attached Files Attached Files
    Regards
    Gerbil (AKA Kevin)

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace from table using VBA (2003)

    Could you not run your Find and Replace twice - once to find and Replace with - then do your 'split' routine? (Or have I missed something?)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace from table using VBA (2003)

    Hi thanks for your reply,

    I got it to work using

    'Replaces any from the PAS data with
    strSQL = "SELECT VisitTime, VisitStage FROM TblAEPASImport"
    Set rec = db.OpenRecordset(strSQL)
    strOld = ""
    strNew = ""
    Do While Not rec.EOF
    strOrig = rec!VisitTime
    rec.Edit
    For intAt = 1 To Len(strOrig)
    If Mid(strOrig, intAt, 2) = strOld Then
    strAltered = strAltered & strNew
    intAt = intAt + (Len(strOld) - 1)
    Else
    strAltered = strAltered & Mid(strOrig, intAt, 1)
    End If
    Next intAt
    rec.Edit
    rec!VisitTime = strAltered
    strAltered = Empty
    rec.Update
    rec.MoveNext
    Loop

    Then it does the split routine.

    Thanks
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •