Create an ID from the first letter of each word in a string

[Russell Melmed]

I have been struggling with this one for some time now in Access 2003, so I'm reaching out for answers. I am trying to auto-populate an ID field in my form using the first character in each word of a text field, along with the month, day, and year from a date field. I can get the date in there using the Month, Day, and Year functions, and the first character of the first word using the Left function, but have been unsuccessful thus far pulling in the remaining first characters of the remaining words in the string. The string from which I would like to pull can have any number of words in it, but I don't reasonably expect more than 6 words. Help?

Example: Text_Field: My Mom's House Date_Field: 10/21/2009 ID_Field: MMH10212009

[nico5038]

Could spoil your search by posting the required code, but will start with a hint.
Check the Split() command and use that in a function.
Use the " " space as delimiter and use a Dim arr to get the result into an array.
By processing the array the left(arr(intI), 1) would be the way to get the first character.

Enough or more needed?

Nic;o)

[Russell Melmed]

Thanks nico,
but I am a novice in creating my own functions and in using arrays. I understand the concept of splitting the text into pieces of an array, with each piece corresponding to one word of my text field, and then using the left function to get the first character of each piece of the array, but I lack the knowledge to apply that concept in VBA. So yes, please more help. Don't worry about spoiling my search.
Russ

[nico5038]

OK, just open a module (or create a new one) and copy/paste this code:

Function fncGetFirstChar(strIn As String) As String

Dim arr
Dim intI As Integer

arr = Split(strIn, " ")
For intI = 0 To UBound(arr)
   fncGetFirstChar = fncGetFirstChar & Left(arr(intI), 1)
Next
End Function

Now you can use in a query or unbound field:

=fncGetFirstCha r([textfield]) & format([datefield],"mmddyyyy")

To get the desired result.

Nic;o)

[Russell Melmed]

As far as I'm concerned, how that works is like magic, but it works. Can you recommend any online tutorials on using arrays in user-defined functions? Thanks for your assistance, Nico, very helpful.

[nico5038]

Glad I could help Russ.
You can check http://bytes.com/topic/access/insights/ for some general tips and tricks and just checkout the VBA statements in the help file to get an idea of the possibilities.

Nic;o)