Solved

Question about INSTR  - need to extract data MS ACCESS query

Posted on 2016-10-17
6
53 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now