?
Solved

Question about INSTR  - need to extract data MS ACCESS query

Posted on 2016-10-17
6
Medium Priority
?
103 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
  • 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 23
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
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.

 
LVL 23

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

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!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

850 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