How do I write a function to return Initials from a Name string

[jacquid]

Using ODBC I am connecting to an external system which does not store the client initials only the "given_names".

I would like to write a function that will return the initials.

The other painful method is to extract them in query one initial at a time but this it time consuming and I need to use this function frequently.

 

[culshaja]

If the names are in sepertae fileds you just use Left$ function. If it is complete name then you will have to use Left$ in conjubction with Instr to find the space between names. If you want a fuller description and example just post here and I'll put one up if you give a complete description of your data.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

What I wanted to do was write one "re-useable" function in VB! Currently I am using Instr, left & mid - this works fine. I want to do something a little more involved that I can re-use with little effort.

 

[culshaja]

Just put this logic into a function in a module and have it as a string as a parameter. You can then just call this function in your queries.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

I have never used modules before and my VB is extremely limited. Could you give an example of how to achieve this?

 

[culshaja]

Here goes:

Public Function Initials(strName as String) As String
 
  dim strInitials as string

 'do the left$, instr$ stuff here and put result into variable

  strInitials = UCase(Left$(strName, 1))
  strInitials = strInitials & UCase(Left$(Instr(...etc.

  Initials = strInitials

End Function

Hope this gives a reasobale starting point.

james James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

Thanx,
I created the function but now when I try use it in Query I get the following error: &quot;Undefined function <name> in expression. (Error 3085)&quot; - Help!

I added the function to the query using the build wizard.

 

[culshaja]

Hmm,
did you make sure that the function was declared Public?
Can you post what the SQL looks like - I might be able to find the problem.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

SQL :
SELECT DISTINCT [SwitchConfL-00-MainExtract-Tbl].cl_number, [SwitchConfL-03-AgentInfo-Tbl].given_names, Initials([given_names]) AS Test
FROM ...

MODULE CODE:
Public Function Initials(strName As String) As String

Dim strInitials As String

'do the left$, instr$ stuff here and put result into variable

strInitials1 = UCase(Left$(strName, 1))
numSpace1 = InStr(1, strName, &quot; &quot
strInitial2 = IIf(numSpace1 > 0, Mid(strName, numSpace1 + 1, 1))
numSpace2 = IIf(numSpace1 > 0, InStr(numSpace1 + 1, strName, &quot; &quot)
strInitial3 = IIf(numSpace2 > 0, Mid(strName, numSpace2 + 1, 1))
strInitials = strInitials1 & strInitial2 & strInitial3

Initials = strInitials

End Function

 

[culshaja]

That should work. Is there any way that you could send me a blank copy of the DB to have a look at? If you can then I'll have a go at it.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

unfortunately not it is huge.

 

[culshaja]

ok. I'll try and do something similar here and get back to you.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

What is the difference between Left$() and Left()?

 

[DougP]

Access is sometimes finicky when a function or something is very close to a command word in VBA
Try renaming the function to MyInitials()

As a test try opening the module in design mode.
Then Press ctrl-G to bring up the immediate/debug window
Now in th ebottom half of the window, type
x=Initials(&quot;Mary&quot or x=MyInitials(&quot;Mary&quot and press enter.
then below it type ?x and press enter

If you see &quot;M&quot; below it then the function is working
If you get an error then the function is not recognized in Access

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[culshaja]

Left$ returns a string, Left returns a variant.

james James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

When I try the debug I get the following error on the first line of code - &quot;complie error: Expected: expression&quot;

line of code highlighted - &quot;Public Function MyInitials(strName As String) As String&quot;

 

[culshaja]

Try removing the As String from the end.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jacquid]

After removing the &quot;AS string&quot; I get the following error message:
&quot;complie error: expected variable or procedure, not module&quot;

 

[sylpat]

Hi James

I am having the same problem as Jacquid. I realized you answed this question along time ago, but I am still figthing this problem.
I have imported a table from the HR Database. The names came over as &quot;Last,First M or MiddleName&quot; all in one field with a comma seperating the first and Last name no space and space between the first and middle intital or Middle name.

I would like to extract the names and make three seperate fields: &quot;Lastname&quot; &quot;First&quot; &quot;Middle Initial only&quot;

I was able to extract the names and create two columns &quot;Last&quot; &quot;First M&quot; When I try to extract the Middle initial If there is no middle name or initial it delete the first name and leave the field blank.
Please, Please help I have been up since 6 yesterday morning and haven't went to sleep yet.
Thanks Sylvia