Extract data from one field to another

[DSCartwright]

Hi Guys,

I have a table that has these fields.

Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.

How would i do this through a query?

Thanks

 

[pr2-eugin]

DSCartwright.. Maybe some sample data in the format (using CODE tags) like..

ID    ForeName    SurName      Salutation
1    Paul        Francis        Mr
2    Ande        Brown          Mr

Sample result set what you like to see.. Would help us to understand and provide a more appropriate solution..

 

[DSCartwright]

ID     Full Name     Forename     Surname     Salutation
1      John Smith    John         Smith       Mr
2      Colin Smith                            Mr Colin Smith
3      Carl Berry

This is how it is currently set up, where possible i want to pull the salutation into the surname but when there is no salutation use the full name as there is always a full name. hope this helps.

 

[DSCartwright]

Sorry about formatting i don't know how to get it right

 

[pr2-eugin]

That's okay.. looks alright to read.. However, what is the result set you want to see? I am unable to follow your requirement here..

 

[DSCartwright]

Okay i will try explain a little better.

When records are like this no action needs to be taken because the surname (required field) has been occupied.

ID     Full Name     Forename     Surname     Salutation
 1      John Smith    John         Smith       Mr

When there is a salutation but no surname i need to pull the salutation into the surname field or the last part if its the surname.

 ID     Full Name     Forename     Surname     Salutation
2      Colin Smith                            Mr Colin Smith

So it will look like this.

 ID     Full Name     Forename     Surname     Salutation
2      Colin Smith                 Mr Colin Smith  Mr Colin Smith

If there is no surname or salutation then i need to pull the full name into the surname again if possible just the last part of the full name assuming this is a surname.

ID     Full Name     Forename     Surname     Salutation
3      Carl Berry

And this will look like this

ID     Full Name     Forename     Surname     Salutation
3      Carl Berry                 Carl Berry

hopefully this has clarified things a little better for you.

 

[DSCartwright]

or would i be better using an SQL statement?

 

[pr2-eugin]

In my opinion, Okay, I think a Function is required in order to do this.. I might be not looking something that would be real simple.. If there is some other option, I am sure someone will be able to give an opinion as well..

So, your query will look like..

SELECT getProperName(Surname, Salutation, FullName) FROM theTableName;

Where getProperName is a user defined function like..

Public Function getProperName(surNameVar, salutVar, fullNameVar) As String
[COLOR=Green]'**************************************************************
'   Code to get a Name when no enough information is available
'
' USAGE Exmaple:
'   ? getProperName(Null, "Mr Colin Smith", "Colin Smith")
'     Mr Colin Smith
'   ? getProperName(Null, Null, "Carl Berry")
'     Carl Berry
'Code Courtesy of
'  Paul Eugin
'**************************************************************[/COLOR]
    If Len(surNameVar & vbNullString) = 0 Then
        If Len(salutVar & vbNullString) = 0 Then
            getProperName = fullNameVar
        Else
            getProperName = salutVar
        End If
    Else
        getProperName = surNameVar
    End If
End Function

 

[DSCartwright]

Not meaning to sound stupid but where should i put the IF statement?

 

[pr2-eugin]

Not meaning to sound stupid but where should i put the IF statement?

What If statement?

You copy the code into a Module, Save it by giving a name OTHER than getProperName. Compile it. Then use it in your Query..

 

[DSCartwright]

That has worked perfectly. thank you very much

 

[pr2-eugin]

Glad to have helped mate.. Good Luck..