Solved

MS Access Generate a code

Posted on 2012-03-28
11
493 Views
Last Modified: 2012-04-03
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
Comment
Question by:GegH
  • 6
  • 3
  • 2
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37776206
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37776222
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37776232
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:GegH
ID: 37800290
I seem to get an Run-Time error: '13'
Type Mismatch
Type-Mismatch.jpg
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37800499
Try typing the variables

dim vArr() as string, j as integer, strL as string, xStr as string
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37800519
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
 

Author Comment

by:GegH
ID: 37800646
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37800698
hmm, that is a curly quote, hang on...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37800720
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37800741
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
 

Author Closing Comment

by:GegH
ID: 37804630
Awesome, thankyou so much.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question