Solved

Multiple criteria with InStr function?

Posted on 2004-08-03
13
2,093 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
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
Comment Utility
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
Comment Utility
Did you copy his function IsWordThere into a standard module (not a form)?

Steve
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DanielAttard
Comment Utility
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
Comment Utility
where are you trying to call it from? If a query please post the SQL here
0
 

Author Comment

by:DanielAttard
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

11 Experts available now in Live!

Get 1:1 Help Now