Results 1 to 5 of 5

Thread: data conversion

  1. #1
    Join Date
    Feb 2004
    Posts
    99

    Unanswered: data conversion

    In totally non-technical terms....

    I have a field (memo format) in a table that has a list of execs delimited by a carriage return as in John Smith, EVP Marketing

    Is there any way I can reformat these exec fields to delimit them in another form or table so that it could be delimited by FirstName - Surname - JobTitle

    The aim is to have a mailing list output from the table but to have little maintenance of the (now) FirstName - Surname - JobTitle delimited field. I would continue to update the list of execs in the original form with the carriage return delimited form

    Any help very welcome and many thanks.
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    Hi Pacha,

    Hope you're ready to start digging into some string functions. As an example, I created a quick table with a single memo field called 'test' with a value of "Kit Lemmonds, Financial Analyst"

    Against this table, I run the following query:

    SELECT InStr([test],",") AS commaPosition, Right([test],Len([test])-InStr([test],",")-1) AS RightOfComma, Left([test],InStr([test],",")-1) AS LeftOfComma
    FROM test;

    commaPosition basically spills out the integer position of the comma in the string. RightOfComma and LeftOfComma return the strings on the corresponding side of the comma. The "-1" is necessary because you probably don't want to return the actual comma itself. The RightOfComma string might be easier to get with a MID() function, as in Mid([test],(InStr([test],",")+1, Len([test]). I guess it depends on whether you are naturally more comfortable subtracting or adding.

    Your last step for the mailing list would be to concatenate with " - " using the "&" or "+" operators.

    The results of my sample query are attached.

    Good luck.
    Attached Thumbnails Attached Thumbnails Clipboard01.jpg  

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    Use a Find and Replace function

    if you are using access 97 then you need to add this

    Code:
    ''************ Code Start **********
    'This code was originally written by Alden Streeter.
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code Courtesy of
    'Alden Streeter
    '
    
    Function FindAndReplace(ByVal strInString As String, _
            strFindString As String, _
            strReplaceString As String) As String
    Dim intPtr As Integer
        If Len(strFindString) > 0 Then  'catch if try to find empty string
            Do
                intPtr = InStr(strInString, strFindString)
                If intPtr > 0 Then
                    FindAndReplace = FindAndReplace & left(strInString, intPtr - 1) & _
                                            strReplaceString
                        strInString = Mid(strInString, intPtr + Len(strFindString))
                End If
            Loop While intPtr > 0
        End If
        FindAndReplace = FindAndReplace & strInString
    End Function
    
    '************ Code End **********
    Newtext = FindAndReplace(FieldName,",","-")
    if using access 2000 +

    can use the Replace Function.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2004
    Posts
    99
    Thanks Stephan, as you say it is clear as mud!!

    Main thing is that you have started me off in the right direction and proved it can be done, now I just need to look at manuals, tutes and get my head round making the waters clearer!!

    Thanks again,

    David
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    pachamama buzz me if you need help

    Your can run the FindAndReplace function in a Query

    Hope that helps
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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