Code Query Based On Multiple Parameters (1 Viewer)

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
Im trying to calculate a value based on input to a field, the calculation in normal terms is the following

<= 8 output is 3
=>9 but <= 11 output is 1
=>12 but <= 20 output is 0
=>21 but <=24 output is 2
>=25 output is 3

I have tried various combinations of the code below noe of which give a complete effect

Private Sub Resp_Rate_AfterUpdate()

RR = Me.Resp_Rate

If RR <= 8 Then Me.RRC = 3
If RR >= 9 <= 11 Then Me.RRC = 1

'If RR = 9 <= 11 Then Me.RRC = 1
'If RR >= 12 <= 20 Then Me.RRC = 0
'If RR >= 21 <= 24 Then Me.RRC = 2
'If RR >= 25 Then Me.RRC = 3

End Sub

RR is the input value,, RRC is where the ouput value ends up I think Im missing an else or maybe
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:15
Joined
Feb 19, 2013
Messages
16,553
try

Code:
Select Case RR
    Case is <=8
        RRC=3
    Case is <=11
       RRC=1
    Case is <=20
       RRC=0
    Case is <=24
       RRC=2
    Case Else
      RRC=3
End Select

note for a RR value of 7, the first option is chosen, the subsequent options do not get a look in so no need for >=
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:15
Joined
Apr 27, 2015
Messages
6,286
Have you considered a Function with a Case statement?
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:15
Joined
Jan 23, 2006
Messages
15,364
Here is a mockup of your conditions.

Code:
' ----------------------------------------------------------------
' Procedure Name: testcases
' Purpose: Mock up of select case to asist
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 15-Sep-20
' ----------------------------------------------------------------
Sub testcases()
          'Private Sub Resp_Rate_AfterUpdate()
          Dim rr(5) As Single, rrc As String, i As Integer, resp_rate As Single
          'rr = Resp_Rate
10        rr(0) = 12
20        rr(1) = 0.09
30        rr(2) = 10.87
40        rr(3) = 14.67
50        rr(4) = 5.933
60        rr(5) = 23.2
70        For i = LBound(rr) To UBound(rr)
80            resp_rate = rr(i)
90            Select Case resp_rate
                  'If RR <= 8 Then Me.RRC = 3
                  'If RR >= 9 <= 11 Then Me.RRC = 1
                  'If RR = 9 <= 11 Then Me.RRC = 1
                  'If RR >= 12 <= 20 Then Me.RRC = 0
                  'If RR >= 21 <= 24 Then Me.RRC = 2
                  'If RR >= 25 Then Me.RRC = 3
                  Case Is <= 8
100                   rrc = 3
110               Case 9 To 11
120                   rrc = 1
130               Case 12 To 20
140                   rrc = 0
150               Case 21 To 24
160                   rrc = 2
170           End Select
180           Debug.Print resp_rate & "   " & rrc
190       Next i
End Sub

Result:
12 0
0.09 3
10.87 1
14.67 0
5.933 3
23.2 2

Update: Geez, I should go back to the forum when dabbling with an example. Oh well, perhaps better late than never.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:15
Joined
Feb 19, 2013
Messages
16,553
perhaps better late than never.
doesn't matter - it demonstrates a different way to use a case statement for ranges.

also demonstrates utilising a test process to check it works as required - something which many people don't do, just say 'it doesn't work'
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:15
Joined
Oct 29, 2018
Messages
21,358
Hi. When you have to compare something to more than one value, you'll have to do it more than once. For example.

Code:
If RR>=12 OR RR<=20 Then...
 

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
Hmm so the first section with RR worked a dream im clearly not understanding the case as applying to next set didnt work
Private Sub SPO2_Scale_1_AfterUpdate()

O21 = Me.O21C


Select Case O21
Case Is <= 91
O21C = 3
Case Is >= 92
O21C = 2
Case Is <= 93
O21C = 2
Case Is >= 94
O21C = 1
Case Is <= 95
O21C = 1
Case Is >= 96
O21C = 0
Case Else
O21C = 3
End Select


End Sub

Im on line two O2 scale 1 the figures remain at 3
 

Attachments

  • download.jpg
    download.jpg
    11.1 KB · Views: 99

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:15
Joined
Aug 11, 2003
Messages
11,696
Stealing / copy paste is harder than it looks

Select case will return the first answer that is true, >= 92 will always be the highest value....
There is no need in this case to have a >= ....

You only want to use the upper ranges here and us < or <= depending on where you want the exact values.

Something like:
Code:
Select Case O21
Case Is <= 92
O21C = 3
Case Is <= 93
O21C = 2
Case Is <= 95
O21C = 1
Case Else
O21C = 0
End Select
 

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
Yes agreed, its a lot harder the case works like a dream so many thanks folowing on from the first post I tryed to seach the net but found it a little bit of a mine field in working out the case and operators so did to replicate
 

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
Sorry so the RR first question works a dream now I have played with SPo2

The value chages once then stops unsure if its my case woking or a glitch else where

Private Sub SPO2_Scale_1_AfterUpdate()

O21 = Me.SPO2_Scale_1

Select Case O21

Case Is < 96
O21C = 1
Case Is <= 93
O21C = 2
Case Is <= 91
O21C = 3
Case Else
O21C = 0

End Select


End Sub
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:15
Joined
Aug 11, 2003
Messages
11,696
You are not outputting your O21 or O21C anywhere from the function...

Also your logic is changed from my post, try to think what happens when your O21 = 90 ???
When O21 = 90, it never gets past <96, the first case, because 90 < 96

Perhaps for your "brain" if then else and between will work better....
Code:
If O21 between 93.000000001 and 96 then O21C = 1
Elseif O21 between 96.00000001 and 100 then O21C = 0
elseif O21 between 10 and 93 then O21C  = 3
else O21C = 99
end
Between works as <= and >= which is why you have to work with the .0000001 to exclude the exact values.

Another idea may be to work like this:
Code:
If O21 > 93 and O21 <= 96 then O21C = 1
Elseif O21 > 96 and O21 <= 100 then O21C = 0
elseif O21 > 10 and O21 <= 93 then O21C  = 3
else O21C = 99
end
this time you dont need to worry about the exact values since you can control the <= or <

Also in either of these methods you can be explicit, and are not dependant on the logical order of things in the case.... Offcourse your can do simularly things in the case.... but I think the IF construct may be a little easier in your train of thoughts.
 

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
yes agreed now I stood back and looked saw the error of my ways,,

can you not use case when handling decimals eg 36.1
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:15
Joined
Aug 11, 2003
Messages
11,696
can you not use case when handling decimals eg 36.1
not use case? What do you mean?

Case is the same thing as IF, just looks different and works a little different (mostly in our brain).
To the computer it is the same thing.... You can compare decimals, integers, strings or dates anything you like in both.

Does that answer the question?
 

dubiousp

Member
Local time
Today, 23:15
Joined
Mar 6, 2020
Messages
86
Sorry just to explain when using whole numbers the below functions

Select Case TMP

Case Is <= 35
TC = 3
Case Is <= 36
TC = 1
Case Is <= 38
TC = 0
Case Is <= 39
TC = 1
Case Is >= 40
TC = 2
Case Else
TC = 0

End Select

On trying to enter a decimal for example in code the following occurs

35#

I think I have just relised that you no need to enter 35.0 but if you enter 35.1 works
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:15
Joined
Aug 11, 2003
Messages
11,696
well it depends a little bit.

On what type TMP is in this example, if it is an integer type field it wont accept decimals...
If it is for example a double, it will.

You are right <= 36, shoudl also pickup 35.99
 

Users who are viewing this thread

Top Bottom