Can you use CASE and INSTR together ?

P

pbennison

Registered User.
Local time
Today, 23:10
Joined
Jan 22, 2014
Messages
19
Hi all,

Newbie here. am writting some VBA for MS ACCESS and wondered if there is a better way of writting some code.
I have a check for lots of different data in a string and wondered if i can use CASE or similar.
Sample code reads...

If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"

Your thoughts would be appreciated

Cheers

Phil
 
SELECT Case is very efficient over multiple/multi tier IF statements.
 
Thanks PR,

How would i use it, in this instance ?

Phil
 
The code you provided does not make much sense, what is the criteria that you are trying to do, give a small sample I will help you out. In general,
Code: 
Select Case sampNum
    Case 1 To 9
        Debug.Print "Number is single digit"
    Case 10
        Debug.Print "Number is 10"
    Case 11, 12, 13, 14
        Debug.Print "Number is > 10, but < 15"
    Case Else
        Debug.Print "Number is >= 15"
End Select
 
pbennison said:
If InStr(1, Me.txt_sp, "London", 1) Then country = "UK"
If InStr(1, Me.txt_sp, "The Capital", 1) Then country= "UK"
If InStr(1, Me.txt_sp, "LDN", 1) Then country= "UK"
If InStr(1, Me.txt_sp, "Brighton", 1) Then country= "UK"
If InStr(1, Me.txt_sp, "Calais", 1) Then country= "France"
If InStr(1, Me.txt_sp, "Paris", 1) Then country= "France"
If InStr(1, Me.txt_sp, "Pariss", 1) Then country= "France"
Click to expand...

This is the sample code. I have changed the search string to hopefully make it clearer
Basically the data i am being given is from a free text field and as such contains different spellings and ways of writting it.
I am using Instr to search a string to find a number of different phrases and depending on is found will set a variable.
 
Code: 
Select Case Me.txt_sp
    Case "London", "The Capital", "LDN", "Brighton"
        country= "UK"
    Case "Calais", "Paris", "Pariss"
        country= "France"
End Select
Would you not be better of with a Table and its related values? So you can maintain it easily? And also the result can be obtained with simple DLookup?
 
Pr,

Thanks for that, but your case example doesnt use the INSTR command.
The text could be anywhere in the string hence why i am using the INSTR command.
I am familiar with case but was wondering if i can use CASE and INSTR together
 
I think maybe i havent explained my self properly. The data comes from an outside source inported into a table. I cannot change the data so i need a way to pick out the data that i need. In order to do that i need INSTR. I just wondered if i can use CASE rather than having lines and lines of If INSTR(....)

Looks like its not possible

Thanks anyway
 
INSTR returns 0 if the text is not found. Something like this will work for you. This way you only need separate if statements for the different results:

Code: 
Dim strSearch As Variant
Dim strUK As String
Dim strFrance As String

strUK = "London;The Capital;LDN;Brighton"
strFrance = "Calais;Paris;Pariss"

strSearch = Split(strUK, ";")
If IsInArray(Me.txt_sp, strSearch) Then country = "UK"

strSearch = Split(strFrance, ";")
If IsInArray(Me.txt_sp, strSearch) Then country = "France"

Then in a public module place this:

Code: 
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
 
I have put together a database with three increasingly complex functions:

[Post 1 of 3]

1) selectLike1() - Hardcoded values for the strings to be matched.

Code: 
Public Function selectLike1(ByVal Haystack As Variant)
Dim TM As String

TM = ""
If Len(Nz(Haystack)) = 0 Then Exit Function

Select Case True
Case Haystack Like "*Give & Take*"
    TM = "Standard"

Case Haystack Like "*Give Take*"
    TM = "Standard"
    
Case Haystack Like "*give and take*"
    TM = "Standard"
    
Case Haystack Like "*Give and*"
    TM = "Standard"

Case Haystack Like "*priority working*"
    TM = "Standard"

Case Haystack Like "*priority boards*"
    TM = "Standard"

Case Else
    TM = "2 Way"
End Select

selectLike1 = TM
End Function

Query: qryTest001
Result:
Code: 
Field1				Result
123 priority working		Standard
8989 priority working kjhk	Standard
give & take			Standard
give & take 1			Standard
give & take 2			Standard
give & take 3			Standard
give and			Standard
give and take			Standard
give take			Standard
kjhkiiuiu			2 Way
oiao  oiu o0			2 Way
priority boards			Standard
priority working		Standard
sdfgadgp9iu 9aur 9uo		2 Way
uhkjh ;ljijo			2 Way
ygju priority b0ards kjhk	2 Way
 

Attachments

[Post 2 of 3]

2) selectLike2() - Strings to be matched read from field phrase of tblMatchPhrases and hardcoded results.

Code: 
Public Function selectLike2(ByVal Haystack As Variant, Optional ByVal doReset As Boolean = False)
Static match2Array() As String, match2ArrayIndex As Long, blAlready2Loaded As Boolean
Dim rstMatchPhrases As Recordset, lngIndex As Long

  ' If the blAlready2Loaded flag is False, the array has not been loaded, then set doReset flag to True
  If Not blAlready2Loaded Then doReset = True

  ' If doReset flag is True then (re)load array with matching phrases
  If doReset Then
      
      Set rstMatchPhrases = CurrentDb.OpenRecordset("tblMatchPhrases", dbOpenDynaset)
      
      With rstMatchPhrases
          .MoveLast
          
  ' Set the size of the array to accept the matching phrases
          ReDim match2Array(.RecordCount + 1)
          .MoveFirst
    
          match2ArrayIndex = 0
    
  ' Loop through and read matching phrases from the table into an array
          While Not .EOF
              match2ArrayIndex = match2ArrayIndex + 1
              match2Array(match2ArrayIndex) = LCase(!phrase)
              .MoveNext
          Wend
        
      End With
      
      Set rstMatchPhrases = Nothing
      
  ' Set blAlready2Loaded flag to True
      blAlready2Loaded = True
  End If

  ' Set default return value
  selectLike2 = "2 Way"
  
  ' Loop through matching phrases and alter return value if found
  For lngIndex = 1 To match2ArrayIndex
    If InStr(Haystack, match2Array(lngIndex)) Then selectLike2 = "Standard"
  Next lngIndex
  
End Function

Query: qryTest002
Result:
Code: 
Field1				Result
123 priority working		Standard
8989 priority working kjhk	Standard
give & take			Standard
give & take 1			Standard
give & take 2			Standard
give & take 3			Standard
give and			Standard
give and take			Standard
give take			Standard
kjhkiiuiu			2 Way
oiao  oiu o0			2 Way
priority boards			Standard
priority working		Standard
sdfgadgp9iu 9aur 9uo		2 Way
uhkjh ;ljijo			2 Way
ygju priority b0ards kjhk	2 Way
 
[Post 3 of 3]

3) selectLike3() - Strings to be matched read from field phrase and results read from field result of tblMatchPhrases.

Code: 
Public Function selectLike3(ByVal Haystack As Variant, Optional ByVal doReset As Boolean = False)
Static match3Array() As String, resultArray() As String, match3ArrayIndex As Long, blAlready3Loaded As Boolean
Dim rstMatchPhrases As Recordset, lngIndex As Long

  ' If the blAlready3Loaded flag is False, the array has not been loaded, then set doReset flag to True
  If Not blAlready3Loaded Then doReset = True

  ' If doReset flag is True then (re)load array with matching phrases
  If doReset Then
      Set rstMatchPhrases = CurrentDb.OpenRecordset("tblMatchPhrases", dbOpenDynaset)
      With rstMatchPhrases
          .MoveLast
          
  ' Set the size of the arrays to accept the matching phrases and results
          ReDim match3Array(.RecordCount + 1)
          ReDim resultArray(.RecordCount + 1)
          .MoveFirst
    
          match3ArrayIndex = 0
          
  ' Loop through and read matching phrases and results from the table into arrays
          While Not .EOF
              match3ArrayIndex = match3ArrayIndex + 1
              match3Array(match3ArrayIndex) = LCase(!phrase)
              resultArray(match3ArrayIndex) = !result
              .MoveNext
          Wend
        
      End With
      Set rstMatchPhrases = Nothing
  
  ' Set blAlready3Loaded flag to True
      blAlready3Loaded = True
  End If

  ' Set default return value
  selectLike3 = "2 Way"
  
  ' Loop through matching phrases and alter return value if found
  For lngIndex = 1 To match3ArrayIndex
    If InStr(Haystack, match3Array(lngIndex)) Then selectLike3 = resultArray(lngIndex)
  Next lngIndex
  
End Function

Query: qryTest003
Result:
Code: 
Field1				Result
123 priority working		Standard_6
8989 priority working kjhk	Standard_6
give & take			Standard_1
give & take 1			Standard_1
give & take 2			Standard_1
give & take 3			Standard_1
give and			Standard_2
give and take			Standard_3
give take			Standard_4
kjhkiiuiu			2 Way
oiao  oiu o0			2 Way
priority boards			Standard_5
priority working		Standard_6
sdfgadgp9iu 9aur 9uo		2 Way
uhkjh ;ljijo			2 Way
ygju priority b0ards kjhk	2 Way
 
You must log in or register to reply here.

Similar threads

S
Help with Instr Function
Replies
18
Views
962
CJ_London
C
C
Read stock prices from webpage
Replies
6
Views
742
cpampas
C
Gasman
Solved Function appears to slow report being generated?
Replies
3
Views
477
The_Doc_Man
The_Doc_Man
S
Solved Weird Visual Bug in my Code that Conditionally Hides Columns
Replies
8
Views
505
SparklySpartan
S
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
1,385
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom