isnull not wokring on second check

[tanzania]

Hi, I have the following code. The weird thing is that if both the fields are null or not null then the code works fine but if one or the other is null then i get a type mismatch error. I have no idea whats causing this!!!

                       If IsNull(rs![Price_B]) Then

                        Else
                            rs![Price_B] = Format(IIf((rs![Price_B] > 0), (rs![Price_B] / 100 + 1), (100 / (rs![Price_B] * -1)) + 1), "#0.00")

                        End If

                        If IsNull(rs![Price_H]) Then

                        Else
                            rs![Price_H] = Format(IIf((rs![Price_H] > 0), (rs![Price_H] / 100 + 1), (100 / (rs![Price_H] * -1)) + 1), "#0.00")
                        End If [Code/]

 

[Rabbie]

It is really weird. Both of your If Then Else Endif blocks are completely independent of each other so it shouldn't make any difference if one is Null and the other isn't

 

[gemma-the-husky]

1 - where do yoo get the type mismatch error?

2 - note also your expressions will fail if either value is equal to 0

3 -the other thing is, is what effect does the format statement have

-assuming your values are numeric, then trying to store a string (ie the result of a format statement) will give you a format error surely - although you ought to get the error if both are not null

4. also I presume we arent seeing all the code are we - ie you dont have an rs.edit statement here, - do you have error trapping in this anywhere - can we see all the code

 

[tanzania]

I think that may have been the problem as the following fix to the code works perfectly:
If rs![Price_B] = "" Then


Else
If rs![Price_B] = 0 Then
Else
rs![Price_B] = Format(IIf((rs![Price_B] > 0), (rs![Price_B] / 100 + 1), (100 / (rs![Price_B] * -1)) + 1), "#0.00")
End If
End If

If rs![Price_H] = "" Then

Else
If rs![Price_H] = 0 Then
Else
rs![Price_H] = Format(IIf((rs![Price_H] > 0), (rs![Price_H] / 100 + 1), (100 / (rs![Price_H] * -1)) + 1), "#0.00")
End If
End If

 

[namliam]

Allways check on Null values like so:

Isnull (field) or field = ""

This you allready learned, but sometimes a null is not null but "" dispite everything... Strange habit of Access I think... but still

 

[gemma-the-husky]

well is the price a string or a number. what is its type in the underlying table

it cant validly be "" or 0 as alternatives - you are bound to get problems with this code as it stands