Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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
0
GegH
Asked:
GegH
  • 6
  • 3
  • 2
1 Solution
 
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])
0
 
Dale FyeCommented:
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.
0
 
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
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
GegHAuthor Commented:
I seem to get an Run-Time error: '13'
Type Mismatch
Type-Mismatch.jpg
0
 
Dale FyeCommented:
Try typing the variables

dim vArr() as string, j as integer, strL as string, xStr as string
0
 
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
0
 
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.
0
 
Rey Obrero (Capricorn1)Commented:
hmm, that is a curly quote, hang on...
0
 
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
0
 
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
0
 
GegHAuthor Commented:
Awesome, thankyou so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now