?
Solved

Question about INSTR  - need to extract data MS ACCESS query

Posted on 2016-10-17
6
Medium Priority
?
92 Views
Last Modified: 2016-10-17
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
0
Comment
Question by:joylene6
[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
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41847404
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
 
LVL 22
ID: 41847411
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
 
LVL 1

Author Comment

by:joylene6
ID: 41847472
Crystal--
   The code worked, but when I run my query, the last line in the result field = #error.   Is this avoidable?
ERROR.png
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41847491
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
 
LVL 1

Author Comment

by:joylene6
ID: 41847512
Coachman99--- Your solution also worked.... but I think I would have to write one for every variation.
0
 
LVL 1

Author Closing Comment

by:joylene6
ID: 41847526
Thank you... I also watched your video, very nice.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

762 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