Access Min Max Comparison Yielding Incorrect Results

[MetalCheese]

Hello All,



I am struggling with a program that compares min and max values in a subform with the value in the main form. I compare multiple elements, but the "Ni" element seems to be an anomaly as far as the code results go. When the mins and maxs are, for ex. 4-8, the code seems to work just fine. If main form value is 5, no label change, if the value is 3 or 9 it changes the label to "Low or High" appropriately and backcolors the element to red. BUT, when I have a min and max in the 9-11 or 8-12 range, things get interesting. 10.1 main form value results in "Low Ni", 9.5 results in "High Ni", 9.9 results in "High Ni", 8 results in "Low Ni", 15 results in "Low Ni".

I am confused why it can work with some mins and maxs and not others. It is the same code from the same table of numbers. I check other elements with the same code with no errors thus far.

Any direction would be appreciated. Code is below:

 
Private Sub Form_Current()
[Ni].BackColor = 11468799
[Label4778].Caption = ""

If [Heat Specification Subform1].Form![Ni Min] > [Ni] Then 
    [Ni].BackColor = vbRed
  
    [Label4778].Caption = [Label4778].Caption + " Low Ni " + [Ni] + [Heat Specification Subform1].Form![Ni Min] 
ElseIf [Heat Specification Subform1].Form![Ni Max] < [Ni] Then
    [Ni].BackColor = vbRed
    [Label4778].Caption = [Label4778].Caption + " High Ni " + [Ni] + [Heat Specification Subform1].Form![Ni Max]
   
End If


End Sub

Thank you!

-S

[John_G]

It's possible that Access is getting confused with datatypes. Try forcing all your "if" statement comparisons to numeric with the val() function:

If val([Heat Specification Subform1].Form![Ni Min]) > val([Ni]) Then
[Ni].BackColor = vbRed

[Label4778].Caption = ....
ElseIf val([Heat Specification Subform1].Form![Ni Max]) < Val([Ni]) Then
[Ni].BackColor = vbRed
[Label4778].Caption = ...
End If

HTH

John

[MetalCheese]

Hey John!

Thanks for your help! That cleared up that issue but I ran into another issue with the value of null.


If [Heat Specification Subform1].Form![Ni Min] = Null Then
minholder = 0
Else
minholder = Val([Heat Specification Subform1].Form![Ni Min])
End If

I tried to catch the null argument (sometimes there is no min, or max, etc) but I get a " invalid use of null" error on:

minholder = Val([Heat Specification Subform1].Form![Ni Min])

I was trying to use a min holder to set the value to 0 if null and take the value if not null. Am I missing something? If its not null the value of the min should be ok...I thought.



Thanks again!
-S

[MetalCheese]

Actually - I think I solved it...

If ([Heat Specification Subform1].Form![Ni Min]) <> Null Then
minholder = Val([Heat Specification Subform1].Form![Ni Min])
Else
minholder = 0
End If

Somehow that works vs. what I had before...! Thanks again!!

[MetalCheese]

Argh, wait, its setting everything to 0...so its not checking anything. If its not null...it should take the value, else go to 0....I thought it makes sense.

Please help if poss! :S

[orange]

Nothing is = NULL or <> Null
NULL is indeterminate/unknown

You can say if IsNull(variableX) then....

Also, a comment:
You should avoid using spaces and special characters in filed and object names.

[MetalCheese]

Thank you Orange! Changing that worked like a charm.

I appreciate the help and the comment! I will note that for the future!!!!!

-S