Solved

Multiple criteria with InStr function?

Posted on 2004-08-03
13
2,148 Views
Last Modified: 2006-11-17
Please have a look at the multiple criteria that I am testing for to return the word "Green".  Currently I am checking for 16 different words, but I need to check for about 40 possible words.  Is there a better way for me to do this exercise?  Should I move everything to a function and then call the function from the query?


PinColor: IIf(IsNull([Type]),"Blank",IIf(InStr([Type],"Single family detached")=1 Or InStr([Type],"home")=1,"Yellow",IIf(InStr([Type],"land")=1 Or InStr([Type],"lots")=1 Or InStr([Type],"site")=1,"Brown",IIf(InStr([Type],"Indus")=1 Or InStr([Type],"Warehousing")=1 Or InStr([Type],"gravel")=1,"Red",IIf(InStr([Type],"Retail")=1 Or InStr([Type],"Office Building")=1 Or InStr([Type],"Commercial")=1 Or InStr([Type],"shopping centre")=1 Or InStr([Type],"office")=1 Or InStr([Type],"auto")=1 Or InStr([Type],"motel")=1 Or InStr([Type],"restaurant")=1 Or InStr([Type],"hotel")=1 Or InStr([Type],"bank")=1 Or InStr([Type],"medical")=1 Or InStr([Type],"dental")=1 Or InStr([Type],"store")=1 Or InStr([Type],"dealership")=1 Or InStr([Type],"marina")=1 Or InStr([Type],"clubs")=1,"Green",IIf(InStr([Type],"Multi-Res")=1 Or InStr([Type],"Rental Apartments")=1 Or InStr([Type],"condo apartments")=1,"Blue","Other"))))))

Thanks for the help.
0
Comment
Question by:DanielAttard
  • 6
  • 4
  • 3
13 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 11706516
define a list of words to check

e.g.

sCheckWords = "Single Family Detatched;Home;Land..."

delimit by semi colon

then use Split to check it

public function IsWordThere(byval sCheckThisWord as String) AS Integer

    dim sWords() as string
    dim i as integer

    IsWordThere = 0

    sWords = Split(sCheckWords,";")
    for i = 0 to ubound(sWords)
        if instr(1,sCheckThisWord,sWords(i)) > 0 then IsWordThere = 1
    next i
end function


now u call this function, passing in the string u want to interrogate
then using your predefined list, go thru and check the words

u could convert both to upper or lowercase to remove case sensitivity

function returns 1 if match found, 0 if not

u can use this in your query or as normal VBA functionality
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11706546
Ive got to go now, but hopefully u know what I mean by this

something like this perhaps (though test in VBA first)

IIF(IsNull([Type]),"Blank",IIF(IsWordThere([Type])=1,"Blue","Other")


in vba tets

public sub TestCode()
   msgbox IsWordThere("Home")
end sub
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11706985
Is Type a free text field? Perhaps create a seperate table that is a list of Types and their associated color. If Type is free text then you could add a color field to your base table and update that field when a new property record is added or changed rather than forcing the function to run for every record everytime you want to run a report.

I would still build a seperate table for each word to Check and their color so finding will be easier and the list of words is easier to maintain. I am guessing that color is something you want to use to change the disply of a form or a report ... you can return the dtandard vbColor constants and use that value directly.

Public Function FindColor(PropertyType As Long) As Long
    FindColor = DLookup("ColorID", "tblPropertyColor", "InStr([Type], " & PropertyType & ")>0")

   ' you could also use a recordset object if you don't like using DLookup
    FindColor = CurentDB.OpenRecordset( _
    "SELECT ColorID FROM PropertyColor WHERE InStr([Type], " & PropertyType & ")>0").Fields(0).Value

End Function

so in your form ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me!ColorID = FindColor(Me.txtPropertyType.Value)
End Sub

Steve
0
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.

 
LVL 39

Expert Comment

by:stevbe
ID: 11707048
right ... forgot about displaying in a form .... because you are storing the color with the proeprty record itself then you only need to be concerned about when you are on a new record

Private Sub Form_Current()
    If Me.NewRecord = True Then
        Me.txtPropertyType.ForeColor = vbBlack
    Else
        Me.txtPropertyType.ForeColor = Me.PropertyColor
    End If
End Sub


for a report you would use the format event for the section the Type field is displayed ... likely the Detail section

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtPropertyType.ForeColor = Me.PropertyColor
End Sub

Steve
0
 

Author Comment

by:DanielAttard
ID: 11708016
Thanks for the ideas.  I  started out by trying to implement rockiroads suggestion and I keep getting an error message saying :

Undefined function 'IsWordThere' in Expression

What do you think this could be?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11708157
Did you copy his function IsWordThere into a standard module (not a form)?

Steve
0
 

Author Comment

by:DanielAttard
ID: 11708174
Yes, I copied it to a standard module.  Can't figure out why I cannot seem to call it.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11708290
where are you trying to call it from? If a query please post the SQL here
0
 

Author Comment

by:DanielAttard
ID: 11708456
I am trying to call it from a query.  Here is the SQL:

SELECT tblSales.Type, IsWordThere("hotel;motel;commercial") AS x
FROM tblSales
WHERE (((tblSales.Type) Is Not Null));
0
 
LVL 39

Accepted Solution

by:
stevbe earned 250 total points
ID: 11708766
ok ... the more I read through where Rocki was going I thjink we will need to expand this a bit ... copy all of the follwing into a module, make additional Const for each color. For each Const you add copy the For... next loop and change the clor constant name and the color name being returned ...

Option Compare Database
Option Explicit

Private Const YellowWords As String = "Single family detached;home"
Private Const BrownWords As String = "land;lots;site"


Public Function GetColor(FindWordInsideMe As String) As String

    Dim astrWords() As String
    Dim i As Integer
   
    'go through all the yellow words
    astrWords = Split(YellowWords, ";")
    For i = 0 To UBound(astrWords)
        If InStr(1, FindWordInsideMe, astrWords(i)) > 0 Then
            GetColor = "Yellow"
            Exit Function
        End If
    Next i
   
    'go through all the brown words
    astrWords = Split(BrownWords, ";")
    For i = 0 To UBound(astrWords)
        If InStr(1, FindWordInsideMe, astrWords(i)) > 0 Then
            GetColor = "Brown"
            Exit Function
        End If
    Next i
   
    'if we get here then no color was found so make it black
    GetColor = "Black"
   
End Function


now in your query ...
SELECT tblSales.Type, GetColor([tblSales].[Type]) AS x
FROM tblSales
WHERE (((tblSales.Type) Is Not Null));

Steve
0
 

Author Comment

by:DanielAttard
ID: 11710160
Wow!  That's cool.  

Thank you, thank you, thank you.

Absolutely brilliant.

Thanks Steve.  Exactly what I was looking for.  
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11710407
It is comments like this that keeps us coming back ... worth way more than points.

If I could be so bold ... if this solution works for you, please split the points between rocki and me.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11713526
Hi Daniel, sorry about yesterday, had to leave and didnt get a chance to catch up on this
and thanks to steve for sorting Daniel out, and the offer for the splitting of points, didnt have to mate, but thanks anyway

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
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.

831 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