Solved

Question about INSTR  - need to extract data MS ACCESS query

Posted on 2016-10-17
6
55 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 19
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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