>0 not working with iif condition, fields with text data type

[dwight1]

Hi All,

I have an expression that is

ExpStep1: IIf([actual loss] Not Like "",[actual loss],IIf([estimated loss] Not Like "",[estimated loss],IIf([exposurecalc] Is Not Null,[exposurecalc],IIf([exposurecalc] Is Null,"No Appraised Value"))))

Actual Loss, estimated loss are the fields in the table with text data type (i do not have control over how the data is created and imported as access table). ExposureCalc is a simple calculation that multiplies a field with .75.

Now i have a result that has some negative numbers and positive numbers. For my requirement i need only the positive numbers.

I tried to give a criteria >0 but it does not work as the data type for the above fields is text.

Can any one advise me on how to filter the results of this query?

Thanks

Dwight

 

[Remou]

Is Val any use to you?
IIf([blue]Val([actual loss])>0[/blue],[actual loss],IIf[blue](Val([estimated loss])>0[/blue],[estimated loss],IIf([exposurecalc] Is Not Null,[exposurecalc],IIf([exposurecalc] Is Null,"No Appraised Value"))))

 

[dwight1]

I tried and it still does not work because there is a text field called "No Appraised Value" in it.

Any ideas

 

[MajP]

Another calculated field

Expr1: IIf([ExpStep1]="No Appraised Value",0,CDbl([ExpStep1]))

criteria >0

 

[MajP]

However,
Do yourself a favor and get away from nested iifs. They are slow, unstable, and difficult to write and debug. Build your own public function to clean this all up and use it in your query

Public Function ExpStep1(theActualLoss As String, theEstimatedLoss As String, theExposureCalc As Single) As String
   If Not theActual = "" Then
    ExpStep1 = theActualLoss
  ElseIf Not theEstimatedLoss = "" Then
    ExpStep1 = theEstimatedLoss
  ElseIf Not IsNull(theExposureCalc) Then
    ExpStep1 = theExposureCalc
  Else
    ExpStep1 = "No Appraised Value"
  End If
End Function

Public Function ExpStep2(theActualLoss As String, theEstimatedLoss As String, theExposureCalc As Single) As single
   If Not theActual = "" Then
    ExpStep1 = theActualLoss
  ElseIf Not theEstimatedLoss = "" Then
    ExpStep1 = theEstimatedLoss
  ElseIf Not IsNull(theExposureCalc) Then
    ExpStep1 = theExposureCalc
  Else
    ExpStep1 = 0
  End If
End Function

 

[dwight1]

Majp,

Thank you very much. It solved my problem.

Dwight