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

K

Kathleen

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
 
G

George Nicholson

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,
 
K

Kathleen

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



George Nicholson said:
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,
--
George Nicholson

Remove 'Junk' from return address.

Kathleen said:
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
Click to expand...
Click to expand...
 
J

John Spencer

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
 
K

Klatuu

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


Kathleen said:
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



George Nicholson said:
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,
--
George Nicholson

Remove 'Junk' from return address.

Kathleen said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change the combo box value according to the value in a textbox on the same form 1
Can TRANSFORM be used in a Crosstab Query to show text 4
Referring to text control in a form 3
Exporting Logo from Access 97 to Excel 2000 by copy and pastespecial is stopping Cell A1 selection 3
select from combo box and display a value in text box 3
cannot delete SOME text in a footer 0
Filling in a grid of text boxes using nested loops? 1
looking for range of text in a single cell 0

Top