joylene6
asked on
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
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
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:
*** 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
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
*** 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
ASKER
Crystal--
The code worked, but when I run my query, the last line in the result field = #error. Is this avoidable?
ERROR.png
The code worked, but when I run my query, the last line in the result field = #error. Is this avoidable?
ERROR.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Coachman99--- Your solution also worked.... but I think I would have to write one for every variation.
ASKER
Thank you... I also watched your video, very nice.
Use trim to remove spaces
Then instr
You can combine all in a query if necessaryEg. If (1, instr (trim(replace, what, with),'A')