vb case help

[ccsoupy22]

For simplicity sake, here are the basics of the query I am stumped
on...
Type: Select query
Name: ColorCells

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)
If Identifier = "A" then color Green
If Identifier = "B" then color Blue
If Identifier = "C" then color Red
The final "case" would be a case else. In other words If Identifier
=
to anything besides the previous 20 items the color = clear
Can anyone please assist me with formalizing this code?
Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 4 weeks, but need to complete this before then.

[storrboy]

Could you do this with another table? Said table would contain the
letter id and the colour name. I *think* a left join would match the
ones it can figure out and return nulls for the others. In which case
you could say in a query...IIF(IsNull([FieldSomething]),"Clear",
[ColourField]).
Maybe?

[Larry Linson]

"storrboy" <st******@sympatico.cawrote

Could you do this with another table? Said table
would contain the letter id and the colour name. I
*think* a left join would match the ones it can
figure out and return nulls for the others. In which
case you could say in a query...IIF(IsNull([FieldSomething]),"Clear",
[ColourField]).
Maybe?

I agree that a separate Table, and a join on the "Identifier", would be
best. But, JIC you don't understand "left join", you create a Join by
clicking and dragging the Field to the corresponding Field in the Table to
be joined. If the Table is tblA and the color code/names are in tblB, then
you will click the Join line to highlight it, right-click and select Join
Properties from the dialog, then choose the option "All records from tblA
and only those that match from tblB."

But, if for some reason you feel compelled to NOT use a separate table, in a
standard module in your application, create a public

Function ColorName(ID As String) As String
Select Case ID
Case "A"
ColorName = "Green"
Case "B"
ColorName = "Blue"
Case "C"
ColorName = "Red"
Case Else
ColorName = "Clear"
End Select
End Function

Then in the Query, you have a Calculated Field. If I understand your post,
you want that Field to be called "ColorCells" so type in ColorCells: into
the Field, and follow with ColorName([ put in the name of the Field that
contains the Idenfier ]). As you didn't say, I've assumed you want to
return a text string with the color name; but, if you want to return a color
value, there are built-in constants for some, but not, I suspect for all 20,
so you'll have to determine the value for other than vbRed, vbBlue, vbGreen,
vbYellow, and the others with built-in constants. If I remember correctly,
color values are held in a LongInteger, so there would be some minor change
to the Function.

Larry Linson
Microsoft Access MVP

[Bruce]

On Mar 28, 11:15 pm, "Larry Linson" <[e-mail]:

"storrboy" <[e-mail]

Could you do this with another table? Said table
would contain the letter id and the colour name. I
*think* a left join would match the ones it can
figure out and return nulls for the others. In which
case you could say in a query...IIF(IsNull([FieldSomething]),"Clear",
[ColourField]).
Maybe?

I agree that a separate Table, and a join on the "Identifier", would be
best. But, JIC you don't understand "left join", you create a Join by
clicking and dragging the Field to the corresponding Field in the Table to
be joined. If the Table is tblA and the color code/names are in tblB, then
you will click the Join line to highlight it, right-click and select Join
Properties from the dialog, then choose the option "All records from tblA
and only those that match from tblB."

But, if for some reason you feel compelled to NOT use a separate table, in a
standard module in your application, create a public

Function ColorName(ID As String) As String
Select Case ID
Case "A"
ColorName = "Green"
Case "B"
ColorName = "Blue"
Case "C"
ColorName = "Red"
Case Else
ColorName = "Clear"
End Select
End Function

Then in the Query, you have a Calculated Field. If I understand your post,
you want that Field to be called "ColorCells" so type in ColorCells: into
the Field, and follow with ColorName([ put in the name of the Field that
contains the Idenfier ]). As you didn't say, I've assumed you want to
return a text string with the color name; but, if you want to return a color
value, there are built-in constants for some, but not, I suspect for all 20,
so you'll have to determine the value for other than vbRed, vbBlue, vbGreen,
vbYellow, and the others with built-in constants. If I remember correctly,
color values are held in a LongInteger, so there would be some minor change
to the Function.

Larry Linson
Microsoft Access MVP

Agreed that the separate table idea is best. However, rather than
create one's own function to do this you could use the VBA Switch()
function instead. Similar to Larry's example you might have a
calculated field in your query that looks like the following:

ColorCells:Switch([ColorID]="A","Green",[ColorID]="B","Blue",
[ColorID]="C","Red",IsNull([ColorID],Null,True,"Clear")

One advantage is that there is no need to create a separate function.
In addition, if the value of your ColorID field can ever be Null, the
Switch() function will return a Null whereas the ColorName function
above would crash. If you wanted to return "Clear" when ColorID is
Null, simply remove the ",IsNull([ColorID],Null" clause from your
calculated field.

Bruce

[Larry Linson]

"Bruce" <de***************@gmail.comwrote

Agreed that the separate table idea is best. However,
rather than create one's own function to do this you
could use the VBA Switch() function instead. Similar
to Larry's example you might have a calculated field in
your query that looks like the following:

ColorCells:Switch([ColorID]="A","Green",[ColorID]="B","Blue",
[ColorID]="C","Red",IsNull([ColorID],Null,True,"Clear")

One advantage is that there is no need to create a separate function.

Frankly, I like breaking out the calculation, rather than having 20 items
plus the ELSE in a calculation in the Query. It's easier, at least for me,
to follow in indented code if you have to debug. But, that's a matter of
preference. And, if it were accessing a server DB, performance might
improve if you put it in a stored procedure.

In addition, if the value of your ColorID field
can ever be Null, the Switch() function will
return a Null whereas the ColorName function
above would crash.

Yep, and there's no error handling, either -- it is, after all, merely a
suggestion in a newsgroup, not intended to be a fully-developed application
or a library procedure for immediate insertion into your general library.
Null input can be handled, but, as I said, you are correct, I didn't handle
it in the newsgroup post.

Larry Linson
Microsoft Access MVP

[Bruce]

On Mar 29, 7:26 pm, "Larry Linson" <[e-mail]:

"Bruce" <[e-mail]

Agreed that the separate table idea is best. However,
rather than create one's own function to do this you
could use the VBA Switch() function instead. Similar
to Larry's example you might have a calculated field in
your query that looks like the following:
>
ColorCells:Switch([ColorID]="A","Green",[ColorID]="B","Blue",
[ColorID]="C","Red",IsNull([ColorID],Null,True,"Clear")

One advantage is that there is no need to create a separate function.

Frankly, I like breaking out the calculation, rather than having 20 items
plus the ELSE in a calculation in the Query. It's easier, at least for me,
to follow in indented code if you have to debug. But, that's a matter of
preference. And, if it were accessing a server DB, performance might
improve if you put it in a stored procedure.

In addition, if the value of your ColorID field
can ever be Null, the Switch() function will
return a Null whereas the ColorName function
above would crash.

Yep, and there's no error handling, either -- it is, after all, merely a
suggestion in a newsgroup, not intended to be a fully-developed application
or a library procedure for immediate insertion into your general library.
Null input can be handled, but, as I said, you are correct, I didn't handle
it in the newsgroup post.

Larry Linson
Microsoft Access MVP

The more I think about it, the more I think that for most situations
the idea of a separate function is the better one. It is more code
but it is probably easier to maintain in the long run. If the case
selection were any more complicated than these four or five cases
trying to maintain the Switch() function would probably be more
arduous than necessary.

Bruce

[Larry Linson]

"Bruce" <de***************@gmail.comwrote

If the case selection were any more complicated
than these four or five cases trying to maintain
the Switch() function would probably be more
arduous than necessary.

If memory serves, somewhere in this thread, the original poster said twenty
colors plus the ELSE case... mine was just a few of those to illustrate. An
IIF with twenty options and a fail-safe ELSE would make for a "busy" Query
Field calculation.

Larry Linson
Microsoft Access MVP