MS Access Generate a code

I have a database of plants and wish to generate a code through a query for each plant by identifying the first 2 letters of the first name, first letter of the second name. That is the easy part.

PlantCode: Left([Plant_Name],2) & IIf(InStr(1,[Plant_Name],"
 ")<>0,"") & Mid([Plant_Name],InStr(1,[Plant_Name]," ")+1,1)

I then need to find the First letter of the 3rd and 4th names if they exist ignoring either the apostrophe or quotation marks.

Any ideas.

An example of the list is below

ACER PALMATUM
ACER PALMATUM ATROPUREUM
ACER PALMATUM BEN OTAKI
ACER PALMATUM BLOODGOOD
ACER PALMATUM ‘BURGUNDY LACE’
ACER PALMATUM CRIMSON KING
ACER PALMATUM DISS VIRIDIS
ACER PALMATUM ‘DISS. VIRIDIS’
ACER PALMATUM FIREGLOW
ACER PALMATUM KATSURA
ACER PALMATUM MIRTE
GegHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
it will be better to use UDF codes to do this, place this codes in a regular module

Function getFirst2Letters(varStr)
if varStr & ""="" then  getFirst2Letters=Null : exit function

dim vArr(), j, strL
vArr=split(varStr," ")

for j=0 to ubound(vArr)
    strL=strL & left(vArr(j),2)
next
getFirst2Letters=strL

exit function


*to use the function in your query


PlantCode: getFirst2Letters([Plant_Name])
Dale FyeOwner, Developing Solutions LLCCommented:
Up awful early today Rey.

I think C1's solution is on the mark for this, but what is this 4, 6, or 8 character code really going to get you?

How are you going to use it?  Is it really going to be unique?  I cannot imagine that

ACER PALMATUM

Is the only plant whose 4 character code would be "ACPA", although I could be wrong.
Rey Obrero (Capricorn1)Commented:
oops, correction

Function getFirst2Letters(varStr)
if varStr & ""="" then  getFirst2Letters=Null : exit function

dim vArr(), j, strL, xStr
xStr=replace(replace(varStr,chr(39),""),chr(34),"")   'remove quotation and apostrophe
vArr=split(xStr," ")

strL= left(vArr(0),2)            'get the first two letters from first name

for j=1 to ubound(vArr)
    strL=strL & left(vArr(j),2)
next
getFirst2Letters=strL

exit function
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GegHAuthor Commented:
I seem to get an Run-Time error: '13'
Type Mismatch
Type-Mismatch.jpg
Dale FyeOwner, Developing Solutions LLCCommented:
Try typing the variables

dim vArr() as string, j as integer, strL as string, xStr as string
Rey Obrero (Capricorn1)Commented:
try this, remove () from vArr() to become vArr


Function getFirst2Letters(varStr)
if varStr & ""="" then  getFirst2Letters=Null : exit function

dim vArr, j, strL, xStr
xStr=replace(replace(varStr,chr(39),""),chr(34),"")   'remove quotation and apostrophe
vArr=split(xStr," ")

strL= left(vArr(0),2)            'get the first two letters from first name

for j=1 to ubound(vArr)
    strL=strL & left(vArr(j),2)
next
getFirst2Letters=strL
GegHAuthor Commented:
That sort of got it, thankyou so much.

Botanic Name      PlantCode
Acer ‘Autumn Blaze’      Ac‘ABl
Acer ‘Bloodgood’      Ac‘B
Acer campestre      Acca
Acer cappodocium rubrum      Accaru
Acer 'Crimson King'      AcCrKi
Acer palmatum ' Seiryn'      AcpaSe
Acer Palmatum Atropureum      AcPaAt
Acer Palmatum Ben Otaki      AcPaBeOt
Acer Palmatum 'Bloodgood'      AcPaBl
Acer Palmatum Burgundy Lace      AcPaBuLa
Acer Palmatum 'Crimson King'      AcPaCrKi
Acer Palmatum 'Crimson Queen'      AcPaCrQu
Acer palmatum 'Dissectum'      AcpaDi
Acer palmatum 'Dissectum Atropurpureum'      AcpaDiAt
Acer Palmatum Dissectum 'Viridis'      AcPaDiVi
Acer Palmatum 'Fireglow'      AcPaFi

Not quite sure i get it though. the function finds each space then picks the first 2 letters of the word in front of it?

Notice Acer 'Autumn Blaze', shouldn't it remove the quotation?

Is there a way to take the first 2 letters of the first word, then the first letter of each subsequent word?

I hope this doesn't sound ungrateful because this is great so far even if i don't understand it.
Rey Obrero (Capricorn1)Commented:
hmm, that is a curly quote, hang on...
Rey Obrero (Capricorn1)Commented:
here is the revised codes


Function getFirst2Letters(varStr)
if varStr & ""="" then  getFirst2Letters=Null : exit function

dim vArr, j, strL, xStr
xStr=replace(replace(varStr,chr(39),""),chr(34),"")   'remove quotation and apostrophe
vArr=split(xStr," ")

strL= left(vArr(0),2)            'get the first two letters from first name

for j=1 to ubound(vArr)
    strL=strL & left(vArr(j),1)   'get the first letter of each subsequent word

next
getFirst2Letters=strL

End Function
Rey Obrero (Capricorn1)Commented:
here is the code that also remove the curly quotes


Function getFirst2Letters(varStr)
If varStr & "" = "" Then getFirst2Letters = Null: Exit Function

Dim vArr, j, strL, xStr
xStr = Replace(Replace(Replace(varStr, Chr(39), ""), Chr(34), ""), Chr(145), "") 'remove quotation and apostrophe and curly quotes
vArr = Split(xStr, " ")

strL = Left(vArr(0), 2)          'get the first two letters from first name

For j = 1 To UBound(vArr)
    strL = strL & Left(vArr(j), 1) 'get the first letter of each subsequent word

Next
getFirst2Letters = strL

End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GegHAuthor Commented:
Awesome, thankyou so much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.