Extracting an initial of a Surname

[simon4amiee]

Hi guys, is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.

Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir

What I want the query to return:

Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A

 

[plog]

Sort of. I wouldn't name a calculated field the same as the field it is using to caclulate. So, I'd name the resulting field something like PlayerNameInitial.

Looking over your examples, yes that seems possible. You will need a few string functions.

You will use Mid (http://www.techonthenet.com/access/functions/string/mid.php) to extract a substring.

Inside that Mid you will use Instr (http://www.techonthenet.com/access/functions/string/instr.php) to find out what position the comma is in.

Give it a shot and post back here what you have.

 

[simon4amiee]

Struggling,

InStr: InStr([Player Name],",")+2

This retuns the number of the letter Im after ever time, but just to display it as letter, the rest I can do I think.

 

[vbaInet]

Using "Hart, Joe" as an example.

For the initials "J":
You will use both Mid() and Instr(). The second parameter of Mid() requires the starting point and your Instr() code will get that starting point. The third parameter of Mid() is going to be 1.

For the surname "Hart,":
You will use Left() and Instr(). The second parameter of Left() requires the length of the string you want to return starting from position 1. Instr() returns that position.

Makes sense?

 

[CJ_London]

surnameintitial= left([Player Name],InStr([Player Name],",")+2)

 

[jdraw]

Just my $.02.
You have a ", " (comma and single space), constant format.

As has been said,
loop start
Read a record
Find the location of the ", "
Add 2 to that location (start of first name), then
Get the first Character at that new location
'do whatever you want with data you now have)
repeat this loop until you've finished all records.
loop end

 

[gemma-the-husky]

having said all this, you would do much better storing the surname and forenames separately.

then it becomes simply

surname & ", " & left(forenames,1)