Question about INSTR - need to extract data MS ACCESS query

I have a table with various data.   I need to extract the letter - A, B C or B/C.  The problem is some have spaces.... some have - in different spaces

Was trying to use the InStr function..... is that right?  Would prefer not to use code, need to do this in the query... THANKS!


DATA                  RESULT
TEAM - A                  A
TEAM - A                  A
TEAM - A                  A
TEAM - A                  A
TEAM - A                  A
TEAM - A                  A
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - B                  B
TEAM - C                  C
TEAM - C                  C
TEAM - C                  C
TEAM - C                  C
TEAM - C                  C
TEAM - C                  C
TEAM -A                  A
TEAM -B                  B
TEAM -B/C            B/C
TEAM -C                  C
TEAM A                  A
TEAM C                  C
LVL 1
joylene6Asked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
yes, it is -- added an error handler.  
public function GetLetter(pString as string) as string
'161017 strive4peace
   On Error GoTo Proc_Err
   dim iPosDash as integer _
      iPosSpace as integer

   'initalize return value
   GetLetter=""

   iPosDash = instr(pString,"-")
   iPosSpace = instr(pString," ")

   if iPosDash > 0 then
      GetLetter = trim(mid(pString, iPosDash+1))
   else
      if iPosSpace > 0 then
         GetLetter = trim(mid(pString, iPosSpace+1))
      end if
   end if
roc_Exit:
   On Error Resume Next
   Exit function 
  
Proc_Err:
'   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetLetter"

   Resume Proc_Exit
   Resume
end function

Open in new window

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
0
 
COACHMAN99Commented:
Use replace to remove unwanted characters
Use trim to remove spaces
Then instr
You can combine all in a query if necessaryEg.  If (1, instr (trim(replace,  what,  with),'A')
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
instr would be good.  Best would be to create a public function that can be called to return what is after the space/dash, given a particular string

This could be done with an equation but using VBA is easier

>"Would prefer not to use code, need to do this in the query"

query can call the code and pass the source string.  In a query, put this in the Field cell:
Result: GetLetter(Data)

before this can work, the following code would have to be in a general module and saved:
public function GetLetter(pString as string) as string
'161017 strive4peace
   dim iPosDash as integer _
      iPosSpace as integer

   'initalize return value
   GetLetter=""

   iPosDash = instr(pString,"-")
   iPosSpace = instr(pString," ")

   if iPosDash > 0 then
      GetLetter = trim(mid(pString, iPosDash+1))
   else
      if iPosSpace > 0 then
         GetLetter = trim(mid(pString, iPosSpace+1))
      end if
   end if
end function

Open in new window



*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc.

IMPORTANT: do NOT name the module the same as any procedure. Give it a unique name like "mod_whatever" or "bas_whatever"

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
joylene6Author Commented:
Crystal--
   The code worked, but when I run my query, the last line in the result field = #error.   Is this avoidable?
ERROR.png
0
 
joylene6Author Commented:
Coachman99--- Your solution also worked.... but I think I would have to write one for every variation.
0
 
joylene6Author Commented:
Thank you... I also watched your video, very nice.
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.

All Courses

From novice to tech pro — start learning today.