Select Case - and looking to match "some" text in a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kathleen - 15 Dec 2005 19:06 GMT

Here is my Select Case statement - it tests for values in a text field - and
if it matches - then sets the background color. Works like a champ - HOWEVER
- what I really want it to do is to look for the text values - ANYWHERE in
the text field - then set that color.

Ideas???? How can I use the Like function here?

Me![Contract Name (s)].BackColor = 16777215 'White - set as default
Select Case Me![Contract Name (s)]
Case "Linear"
Me![Contract Name (s)].BackColor = 8454143 'Yellow
Case "Ramp"
Me![Contract Name (s)].BackColor = 4259584 'Green
Case "Summit"
Me![Contract Name (s)].BackColor = 12632256 'Grey
Case "Cracker Jack"
Me![Contract Name (s)].BackColor = 16777088 'Light Blue
Case "Marathon"
Me![Contract Name (s)].BackColor = 4227327 'Orange

End Select

Reply to this Message

George Nicholson - 15 Dec 2005 19:29 GMT

Select Case True
Case instr(1, Me![Contract Name (s)], "Linear") > 0
Me![Contract Name (s)].BackColor = 8454143 'Yellow
Case instr(1, Me![Contract Name (s)], "Ramp") > 0
Me![Contract Name (s)].BackColor = 4259584 'Green

(etc.)

If any [Contract Name (s)] value contains more than one of the tested
values, only the first one that tests true will trigger a color change.
i.e., A value of "RampLinear" will turn yellow since Linear tests true
first. At that point the Select Case stops testing the value.

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> Here is my Select Case statement - it tests for values in a text field -
> and
[quoted text clipped - 19 lines]
>
> End Select

Reply to this Message

Kathleen - 15 Dec 2005 19:51 GMT

I'm getting an error - RunError 94 "Invalid use of Null" ...

Here is my new Select Case

Select Case True

Case InStr(1, Me![Contract Name (s)], "Linear") > 0
Me![Contract Name (s)].BackColor = 8454143 'Yellow
Case InStr(1, Me![Contract Name (s)], "Ramp") > 0
Me![Contract Name (s)].BackColor = 4259584 'Green
Case InStr(1, Me![Contract Name (s)], "Summit") > 0
Me![Contract Name (s)].BackColor = 12632256 'Grey
Case InStr(1, Me![Contract Name (s)], "Cracker Jack") > 0
Me![Contract Name (s)].BackColor = 16777088 'Light Blue
Case InStr(1, Me![Contract Name (s)], "Marathon") > 0
Me![Contract Name (s)].BackColor = 4227327 'Orange

End Select

End Sub

> Select Case True
> Case instr(1, Me![Contract Name (s)], "Linear") > 0
[quoted text clipped - 33 lines]
> >
> > End Select

Reply to this Message

Klatuu - 15 Dec 2005 20:08 GMT

That will happen when Me![Contract Name (s)] has had no value entered. You
will need to test for that. Since a Select Case statement only evaluates 1
case at a time starting from the top, this will prevent the InStr function
from trying to evaluate a Null value. Also, Notice the change I made where
you set the color to white:

Select Case True
Case IsNull(Me![Contract Name (s)])
Me![Contract Name (s)].BackColor = 16777215 'White - set as default
Case InStr(1, Me![Contract Name (s)], "Linear") > 0
Me![Contract Name (s)].BackColor = 8454143 'Yellow
Case InStr(1, Me![Contract Name (s)], "Ramp") > 0
Me![Contract Name (s)].BackColor = 4259584 'Green
Case InStr(1, Me![Contract Name (s)], "Summit") > 0
Me![Contract Name (s)].BackColor = 12632256 'Grey
Case InStr(1, Me![Contract Name (s)], "Cracker Jack") > 0
Me![Contract Name (s)].BackColor = 16777088 'Light Blue
Case InStr(1, Me![Contract Name (s)], "Marathon") > 0
Me![Contract Name (s)].BackColor = 4227327 'Orange
Case Else
Me![Contract Name (s)].BackColor = 16777215 'White - set as default
End Select

> I'm getting an error - RunError 94 "Invalid use of Null" ...
>
[quoted text clipped - 54 lines]
> > >
> > > End Select

Reply to this Message

John Spencer - 15 Dec 2005 20:04 GMT

You can use If ..ElseIf...Else...End If structure along with like. This
duplicates the case statement as long as it Case has only one value.

Public Function LikeMatch(strIN)
Dim strMatch As String

strMatch = UCase(strIN)

If strMatch Like "*LINEAR*" Then

ElseIf strMatch Like "*RAMP*" Then

ElseIf strMatch Like "*Cracker Jack*" then

Else

End If

End Function

> Here is my Select Case statement - it tests for values in a text field -
> and
[quoted text clipped - 19 lines]
>
> End Select

Reply to this Message

John Spencer - 15 Dec 2005 20:08 GMT

Whoops! Forgot to check for null

Public Function LikeMatch(strIN)
Dim strMatch As String

strMatch = UCase(strIN) & vbNullString

If strMatch Like "*LINEAR*" Then
Me![Contract Name (s)].BackColor = 8454143 'Yellow
ElseIf strMatch Like "*RAMP*" Then

ElseIf strMatch Like "*Cracker Jack*" then

Else

End If

End Function

End Function
"John Spencer" <spencer@chpdm.edu> wrote in message news:...

> You can use If ..ElseIf...Else...End If structure along with like. This
> duplicates the case statement as long as it Case has only one value.
[quoted text clipped - 39 lines]
>>
>> End Select

Reply to this Message