Help with SELECT CASE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that contains 4 conditions and I need to structure a SELECT
CASE STATEMENT and I do not know how to start it or how to output the results
into a TextBox in my report.


The conditions I have are as follows:

CASE 1
IF [X:50]>34 THEN “WWWâ€

CASE 2
IF [X:50]<35 THEN “XXXâ€

CASE 3
IF [TYPE] = “A†and Not IsNull([X:50]) THEN “YYYâ€

CASE 4
IF [TYPE] = “A†and IsNull([X:50]) THEN “ZZZâ€



Please Help.
 
One additional question:

How would I reference field names in the output?

With a normal TextBox I would do:
"XXX" & [x:50] & "xxx"

But how do I do that in the VBA?
 
I think you need to tweak your conditions slightly. A select case statement
will execute until it finds a true option, then it will action that and go to
the end select. Your criteria could see more than one case being true.

According to your conditions, you could possibly want "WWW" "XXX" and "YYY"
at the same time.

If you mean test for conditions

IsNull, <34, >=34 and type not A, >= 34 and type = A then you could do it as
follows

If IsNull(Me![X:50]) Then
'do option 1
Else
Select Case Me![X:50]
Case Is < 34
'do option 2
Case Is >= 35
If Me!Type = "A" Then
'do option 3
Else
'do option 4
End If
End Select
End If

Note that the select case statement works as "select case <thing to
lookat>". Rather than if, where you specify a condition for each if, select
case assumes you're checking the same thing each time, but looking for
different values.
 
Initially I had a SWITCH command in a text box and here was the syntax:

=switch(
[x:50]>34,"Pass with a score of "& [x:50] & "and earned the title of
"&[title],
[x:50]<35,"Failed with a score of "& [x:50] & " and needs to be retested."

I started to have trouble when I realized i had to include [type] into the
equation.
[TYPE] has 2 options "familiarization" or "qualification"
and the 3rd and 4th cases were as follows:

Case 3
If [TYPE] = "Familiarization" AND Is NotNull([x:50]) then
"This was only "& [type] & " score was " & [x:50]

Case 4
If [TYPE] = "FAMILIARIZATION" AND IsNull([x:50]) then
"Not required to complete"

When I tried to incorporate all 4 conditions into the SWITCH command, I had
trouble.
 
how about this, then...

Select Case Me!Type
Case "Familiarization"
If IsNull(Me![X:50]) Then
Me!Message = "This was a familiarization exercise; not required to
complete."
Else
Me!Message = "This was a familiarization exercise; you scored " &
Me![X:50] & "."
End If
Case "Qualification"
If IsNull(Me![X:50]) Or Me![X:50] <= 34 Then
Me!Message = "Failed with a score of " & Nz(Me![X:50], 0) & " -
needs to be retested."
Else
Me!Message = "Passed with a score of " & Me![X:50] & "."
End If
End Select
 
Back
Top