Trim or Left Function??

[access09]

I am trying to seperate a name field so that only the first part shows.

Data I am trying to seperate looks like this:
John/Mary
Frank/Jennifer
Ben
Jonathan/Janet

I tried using this:

Left([name],InStr([name],"/"))

However, when I use it the 'Ben' entry disappears and the first part of the other entries stay behind but keep the '/' character at the end.

Anyone any ideas to help me?

 

[gemma-the-husky]

well first you need to find the separator, as you are doing - but if there isnt one, you get 0 returned. so in that case you are taking the left part up to position 0, hence you are getting a blank string, which isnt what you want.

so something like the following should work

pos = instr(1,fullname,"/")
if pos>0 then
'take the characters up to the separator
partstring = left(fullname, pos-1)
else
'keep the whole string
partstring = fullname
end if

you can rearrange this to use in a query, with a nested iif statement. using this sort of logic

 

[DCrake]

Public Function GetSurname(AnyText As String) As String

Dim nIndex As Integer

nIndex = InStr(AnyText,"\")

If nIndex = 0 Then
   GetSurname = AnyText
Else
   Getsurname = Left(AnyText,nIndex-1)
End If

End Function

The above first checks for the existance of a "\ " if one is not found it simply returns the whole text otherwise it returns everything upto the position where the backslash was found.

To use this in a query or a form

Form:

Me.TxtSurname = GetSurname(Me.FullName)

Query:

Surname:GetSurname([FullName])

David

 

[access09]

Thats great. It works. Thanks very much

 

[Atomic Shrimp]

Alternatively, you can do it all in one line (although it can be a bit tricky to maintain stuff coded like this)

iif(InStr([name],"/")>0,Left([name],InStr([name],"/")-1),[name])

 

[access09]

Thanks they both work.

How would I got about displaying the second names then and If there is only one name entered it will leave it blank. i.e. the 'ben' entry would be blank?

 

[DCrake]

If the text string does not have a \ in it you have to decide whether this is going to be the first name of the last name. To get the everything after the backslash the function provided needs to be amended as per below.

Public Function GetName(AnyText As String, bFlag As Boolean) As String

Dim nIndex As Integer

nIndex = InStr(AnyText,"\")

If nIndex = 0 Then
   GetName = AnyText
Else
   If bFlag = True Then
      GetName = Left(AnyText,nIndex-1)
   Else
      GetName = Mid(AnyText,nIndex+1)
End If

End Function

By passing another parameter to the function we can tell it which side of the blackslash we are interested in

So in your query you would state

MyForename:GetName([FullName],True)

MySurname:GetName([FullName],False)

Example

Full Name = Bill/Poster

GetName("Bill/Poster",True) returns Bill
GetName("Bill/Poster",False) returns Poster

David

 

[access09]

Thats unreal thanks.

Do you know the way some of the name entries only has one name. i.e Michael

That is coming up twice in both the forename and surname. Is there a way for it to only show up in the forename field?

Ben/Jane Ben Jane
Michael Michael Michael
Tony/Jen Tony Jen

 

[DCrake]

Public Function GetName(AnyText As String, bFlag As Boolean) As String

Dim nIndex As Integer

nIndex = InStr(AnyText,"\")

If nIndex = 0 Then
   If bFlag = False then   'Surname
      GetName = ""
   Else
      GetName = AnyText  'Forename
   End If
   Exit Function
Else
   If bFlag = True Then
      GetName = Left(AnyText,nIndex-1)
   Else
      GetName = Mid(AnyText,nIndex+1)
End If

End Function

 

[access09]

bingo.......thanks. i really appreciate your help

 

[access09]

how about if i had certain characters preceeding the name like '*Paul' and '+Tony'

How do you use the replace function in VBA to get rid of the '*' and '+' characters??

 

[DCrake]

Press Ctrl+G to go to the immediate window and type in the word Replace. Then highlight it and press F1. This will bring up the Access help for the Replace function.

In short replace works like this

Replace("+Fred","+","") returns "Fred"

David

 

[access09]

ok fair enough. one quick question. how would I reference the MyForename field??

Replace([MyForename],"+","") ---- Doesnt work

 

[Brianwarnock]

Replace is a VBA function and that code would work, assuming the names are correct, however why not in the query use
newfld: IIf(Left([fld],1)<"a",Mid([fld],2),[fld])
then any non letter character will be removed.

Brian

 

[access09]

Is there a limit to the amount of replace functions you can use in a single query?

 

[Brianwarnock]

Aren't you going to modify the code provided by DCrake? Therefore we are talking VBA. Why not incorporate my if statement, but yes you could have a series of Replace statements.

Brian

 

[access09]

I dont know how to reference the replace function in VBA, and I cant use the iif statement because Im replacing words aswell as special characters.

I have the following but when I add any more replace functions it crashes saying the query is too complex:

UCase(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(GetName(Replace(Replace(Replace([job],"A/C","Account"),"S/E",""),"&","/"),True),"?",""),"`",""),".",""),"*","")," MGR"," Manager"),"Bank Offical","Bank Staff"),"Bank Official","Bank Staff"),"Bank Porter","Bank Clerk")," BOI",""),"Banker","Bank Staff"),"Bank Assistant","Bank Staff"),"Bank Asst","Bank Staff"),"Bank Ass","Bank Staff"),"Bank Employee","Bank Staff"),"Bank Off","Bank Staff"),"BOI Staff","Bank Staff"),"Beautician","Beauty Therapist"),"Blocklayer","Bricklayer"),"Block Layer","Bricklayer"),"Brick Layer","Bricklayer"),"Book Keeper","Book-Keeper"),"Builders Foreman","Building Foreman"),"Care Worker", "Care Assistant")) AS Job1

Is there a limit on the number of replace functions do you think?

 

[DCrake]

You are going to fall over arse of tit using this approach. Are there and delimiters between the persons job and the beginning of their forename. And where did the data originate. Can you send a sample of the source data. There will be a much easier and faster way. And as this should be only be a once off exercise to get the data split it can be done without finesse.

David

 

[Brianwarnock]

I don't know if there is a limit, but I don't understand the Getname in the middle of the Replace statements, but that could be my ignorance I suppose.

Brian

 

[access09]

I am transfering data from one system to another. A person's occupation on one system is entered by free text but I am moving that data into another system that has a person's occupation entered through a pre-defined list of jobs.

If you look at the MS access table I have a list of occupations and some cases mentions two occupations per entry. I need to split up the entries with more than one occupation. The main thing is to map the occupations from the first system to the second system's predefined list.

In the second system all bank officials etc. are called bank staff and bank managers are just called bank managers. That is why I am using the replace function to change bank officials etc. to Bank staff.

That is the same with the other replace fuctions you see in the query in the post above.