Convert 'Albert Name' into 'A.Name' in Access?

[Rummy]

Anybody know how I can do this? I have a field that is a person's name, but I want to convert it in/on a report(or underlying query) into first initial then Surname. I know how to get the first initial I think(using a Left function) but don't know how to find the space and then take everything after it. I also presume it'd be a case of these two functions and a concatenation.

 

Anybody good with this stuff? For examples,

 

Albert Name -> A.Name

Bobby Bobbertson -> B.Bobbertson

Charlie Cobalt-Smith -> C.Cobalt-Smith

 

Yeah my fake names suck. ONE of my names does has a hyphen in it, but tbh I don't see that causing a problem, right?

[Emasher]

convertedName = Left(originalName,1) & "." & Mid(originalName,Instr(originalName," "))

 

That should do it. I don't know MS access, so there may be a minor syntax error in there, but you should be able to figure it out based on that.

 

Please tell me that this is for work and that you're not actually trying to learn how to program using access.

Edited October 15, 2011 by Emasher

[Rummy]

Hey, what's wrong with trying to teach myself a bit of Access! I see no shame in that.

 

Totes for work though. I found something similar to what you said, I'll take it for a spin next time I'm in and see if it works out cool and stuff. Thanks for the help

[Emasher]

There's nothing wrong with learning access, its just not a great way to learn programming. If it wasn't for work, you'd be much better off learning to program, then applying said knowledge to access afterwards.

[Rummy]

Oh I see what you're saying now. Learning to program will take me far longer, though it's nice learning these little bits along the way.

[Rummy]

Fiddled it a bit but works perfectly, many thanks for that Emasher!