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?

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

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

Thats great. It works. Thanks very much :-)

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])

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?

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

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

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

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

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??

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

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

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

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

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

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

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(Repl ace(Replace(Replace(Replace(Replace(Replace(Replac e(Replace(Replace(Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(Replace(GetName(Re place(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?

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

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

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.

As this is only a one off exercise I would move from trying to update your list in one fail swoop, I would run the update/replace for each replacement required. Using this method at least you can check that each replacement has worked.

If you want to do this code you will need the following


Public Function Revisions(StrFind As String, StrReplace As String)

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")

Do Until Rs.EOF
Rs.Edit
Rs("YourFieldNameHere") = Replace(Rs("YourFieldNameHere"),StrFind,StrReplace)
Rs.Update
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
Debug.Print "Done"

End Function


Then in your immediate window type in

?Revisions("Shirker","Worker")

Repeat for all variations until you have cleaned up your database

David

Thanks. Looks good.

What do you mean by the immediate window actually? Where do I type in the code?

Whilst in the module where you added the function you can press ctrl+g to open up a small pane at the bottom of the screen. This is known as the immediate window (don't ask why) you can run functions from their.

To do this you enter a ? followed by a function name and any parameters that you need to send. So as per my example it would be

? Revisions(BadString,"LookForWhatString","ReplaceWithThisString")

By putting in the line Debug.Print "Done" when the code has been completed it echos "Done" on the next line in the immediate window. This is purely to let you know that the function has completed.

David