Results 1 to 3 of 3

Create a script that replaces all . with , and adds a zero before number in column

  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015

    Create a script that replaces all . with , and adds a zero before number in column


    I have a lot of data in columns that vary a fair bit in how they are named. I need to make make all the data so it has the same format.

    That means that I have to replace some text in the middle of a string. I've added a picture as an example as well.

    I could do a search and replace like approach with the . and , - But adding the 0 before that last number seems way harder, and it is also only before this last one the zero is needed, not anywhere else.



    Any help on an approach or suggestions would be much appreciated.

    Click image for larger version. 

Name:	Access.PNG 
Views:	23 
Size:	27.9 KB 
ID:	36001

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    ?? I suggest you identify exactly what you want to change/replace.
    I see no mention of the rightmost "T" for example.

    For each string
    You want/need all "." to be changed to ","
    If the length of the data after the rightmost "-" is 1, then insert a "0" before that character , then concatenate "-T" to the right end of the string..

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    I worked up a function that will process a string as you've requested based on the examples given. I can't know everything about your situation and dataset so it's obviously untested, but it could give you an idea of an approach you could take:

    Public Function FixMyString(ByVal InputStr As String) As Variant
        Dim pos As Integer 'index position of the last digit(s) in the input string
        Dim InputLen As Integer 'num of chars in the input string
        Dim asciiCode As Integer 'ascii value of the last char in the string to see if it has a T (or anything other than a number)
        'Replace ALL period chars with a comma
        InputStr = Replace(InputStr, ".", ",")
        'Decide where in the sting to look to check if the last digits are 1 digit or two digits
        asciiCode = Right(InputStr, 1)
        InputLen = Len(InputStr)
        If Not (asciiCode > 47 And asciiCode < 58) Then 'If the last char in the string is anything other than a number
            pos = InputLen - 3
            pos = InputLen - 1
        End If
        'If there is hyphen in this position then insert a 0 char because we expected a digit
        If Mid(InputStr, pos, 1) = "-" Then
            InputStr = Left(InputStr, pos) & "0" & Right(InputStr, InputLen - pos)
        End If
        FixMyString = InputStr
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-06-2018, 11:24 AM
  2. Replies: 10
    Last Post: 12-29-2017, 03:06 PM
  3. Replies: 9
    Last Post: 03-02-2015, 05:05 PM
  4. Need to create a script to auto update a field
    By storyiii.5 in forum Programming
    Replies: 1
    Last Post: 08-07-2013, 03:43 PM
  5. Replies: 1
    Last Post: 03-02-2010, 09:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums