Code for all records (1 Viewer)

JRPMD

Registered User.
Local time
Today, 10:29
Joined
Nov 24, 2012
Messages
52
Hello , I have this code and it applies on one record when click on a command button in a form.
If (Me.pH.Value < 7.15) Then
Me.pH_ApII.Value = 4
ElseIf (Me.pH.Value < 7.25) Then
Me.pH_ApII.Value = 3
ElseIf (Me.pH.Value < 7.32) Then
Me.pH_ApII.Value = 1
ElseIf (Me.pH.Value < 7.49) Then
Me.pH_ApII.Value = 0
ElseIf (Me.pH.Value >= 7.5) Then
Me.pH_ApII.Value = 3
ElseIf (Me.pH.Value > 7.7) Then
Me.pH_ApII.Value = 4
End If
Several records are entered from another form and I have to apply the code individually in each record to obtain the results.
It doesn't work with OnOpen Form or AfterUpDate record to make the calculation.
Is there another way that automatically applies the code to all records?
Or even make this calculations in a query?
Thank you very much.
 

isladogs

MVP / VIP
Local time
Today, 17:29
Joined
Jan 14, 2017
Messages
18,186
Use an update query with a reference table with 2 fields pH and ApII.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:29
Joined
May 7, 2009
Messages
19,169
put those values in a Table (tblPHRange)

fields:
LowRange (Number/Double)
HiRange (Number/Double)
Value (Number/Integer)

Code:
example data:
=================================
LowRange      HighRange    Value
---------------------------------
0             7.14         4
7.15          7.24         3
7.25          7.31         1
7.32          7.49         0
7.50          7.77         3
7.77          9999         4
---------------------------------
now on your code, you only need to lookup the value
if it falls in the range:

M.pH-ApII.Value = DLookup("Value","tblPHRange", Me.ph.Value & " Between [LowRange] And [HighRange]")
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 28, 2001
Messages
26,999
Create a public function. You can call that from a query or a form or a report. Note that your inequalities "change direction" in your list. This might not give you the result you wanted since in an IF ladder, first match wins.

Put this in any general module in your project.

Code:
Public Function PHtoAPII( Ph as Single ) as Long

SELECT CASE Ph
    CASE IS < 7.15
        PHtoAPI = 4
    CASE 7.16 TO 7.25
        PHtoAPI = 3
    CASE 7.26 TO 7.32
        PHtoAPI = 1
    etc. {you fill in the blanks here}
    CASE IS > 7.7
        PHtoAPI = 4
END SELECT
End Function

Then you can use

Code:
Me.pH_ApII = PHtoAPII( Me.pH)

to take care of your form's value. And this would also be available on reports or queries as needed as long as the function is public and in a general mdoule.

You can adjust the ranges that I used to whatever codes and boundary values and levels of precision you want and it is perfectly acceptable to use SINGLE or DOUBLE (as appropriate) in the CASE statement evaluation expressions.

With SELECT CASE there is no ambiguity in the ranges and you could actually express them in any order even for the ones that are open-ended. Which means that you can put the most likely cases first to speed up performance ever so slightly.

I also assumed (which of course could be wrong) that you were trying to make some sort of integer code out of these values. But if you needed to return a SINGLE or DOUBLE, that would also be trivial.

Finally, as a point of curiosity, looks like you must be working with blood pH. Did I guess wrong on that? Not relevant to my answer, but it just piqued my interest. And if I'm right then your most likely case is actually the "ApII = 0" case. The CASE statements in the SELECT CASE are evaluated in the order presented, so with the ranges expressed carefully, you can make the function a smidge faster.

Here's a quick reference for SELECT CASE statements.

https://docs.microsoft.com/en-us/do...ge-reference/statements/select-case-statement
 

isladogs

MVP / VIP
Local time
Today, 17:29
Joined
Jan 14, 2017
Messages
18,186
You're welcome but I suggest you use arnel's solution or that by the DocMan.
Both are better than mine
 

JRPMD

Registered User.
Local time
Today, 10:29
Joined
Nov 24, 2012
Messages
52
put those values in a Table (tblPHRange)

fields:
LowRange (Number/Double)
HiRange (Number/Double)
Value (Number/Integer)

Code:
example data:
================================
LowRange      HighRange    Value
---------------------------------
0             7.14         4
7.15          7.24         3
7.25          7.31         1
7.32          7.49         0
7.50          7.77         3
7.77          9999         4
---------------------------------
now on your code, you only need to lookup the value
if it falls in the range:

M.pH-ApII.Value = DLookup("Value","tblPHRange", Me.ph.Value & " Between [LowRange] And [HighRange]")

Thank you , arnelgp.
The table has many other fields , with different variables ranks , so I try to make a separate table for each variable and use your suggestion.
Thank you very much!
 

JRPMD

Registered User.
Local time
Today, 10:29
Joined
Nov 24, 2012
Messages
52
Create a public function. You can call that from a query or a form or a report. Note that your inequalities "change direction" in your list. This might not give you the result you wanted since in an IF ladder, first match wins.

Put this in any general module in your project.

Code:
Public Function PHtoAPII( Ph as Single ) as Long

SELECT CASE Ph
    CASE IS < 7.15
        PHtoAPI = 4
    CASE 7.16 TO 7.25
        PHtoAPI = 3
    CASE 7.26 TO 7.32
        PHtoAPI = 1
    etc. {you fill in the blanks here}
    CASE IS > 7.7
        PHtoAPI = 4
END SELECT
End Function

Then you can use

Code:
Me.pH_ApII = PHtoAPII( Me.pH)

to take care of your form's value. And this would also be available on reports or queries as needed as long as the function is public and in a general mdoule.

You can adjust the ranges that I used to whatever codes and boundary values and levels of precision you want and it is perfectly acceptable to use SINGLE or DOUBLE (as appropriate) in the CASE statement evaluation expressions.

With SELECT CASE there is no ambiguity in the ranges and you could actually express them in any order even for the ones that are open-ended. Which means that you can put the most likely cases first to speed up performance ever so slightly.

I also assumed (which of course could be wrong) that you were trying to make some sort of integer code out of these values. But if you needed to return a SINGLE or DOUBLE, that would also be trivial.

Finally, as a point of curiosity, looks like you must be working with blood pH. Did I guess wrong on that? Not relevant to my answer, but it just piqued my interest. And if I'm right then your most likely case is actually the "ApII = 0" case. The CASE statements in the SELECT CASE are evaluated in the order presented, so with the ranges expressed carefully, you can make the function a smidge faster.

Here's a quick reference for SELECT CASE statements.

https://docs.microsoft.com/en-us/do...ge-reference/statements/select-case-statement

Thanks for your kinded answer Doc Man.
I think it will be usefull for me.
i'm not to skilled in SQL so I will work hard on your suggestion.
Yes I work with blood pH . There are a lot of professional software programas in this area , but I also like to pick up data bedside in my cel phone and then syncronize with Access. This and other variables are in the APACHE II score.
Thank you very much!
 

Users who are viewing this thread

Top Bottom