?
Solved

MS Access Generate a code

Posted on 2012-03-28
11
Medium Priority
?
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

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

Expert Comment

by:Dale Fye
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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

770 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