Compound IF trouble #error

[wcrimi]

I have a field that contains the finish position of a horse race [FinalCall]

I have a field that contains the winning margin [FinalCallLenAdj]

I have a fields that defines a "big win" [adjbigwin]


Anyone see anything wrong with is statement?

bigwin: IIf(([FinalCall]=1) And ([FinalCallLenAdj]>[adjbigwin]),3,0)


If the finish position is =1 and the winning margin is greater than the big win definition, I want to return a value of 3, otherwise return zero.

Whenever the finish position is = 1 it's returning #error and the rest of the time it's returning a zero.



Thanks

[ItsMe]

Seems like your are using field names that execute an agg calc. You will need to include the results of the calculation in your IIf or include the function that is the agg calc in your IIf.

It is likely the field names, by themselves, are not anything. They are empty.

[wcrimi]

That's not it. Each of those fields is available in one of two different tables that are both input to this query and will always have a value.

Talking out loud here. Could it be that one of them is not defined as numeric even though it has numeric values in it and that's screwing up the ">" comparison?

[ItsMe]

You can use the greater than operator as comparison of Text fields, but it may not give you the results expected if you are comparing anything other than Null or an empty string.

[wcrimi]

Hmmmn, then I don't know what the problem could be. :-(

[ItsMe]

Hmmmn, then I don't know what the problem could be. :-(

Are you saying the fields are data type text? If there are numbers within a text field you could use the field name as an expression within the CLng() function
https://msdn.microsoft.com/en-us/lib.../gg278896.aspx

[wcrimi]

I found the problem. [adjbigwin] was defined as a "text" field with values like 4, 3, 2.5, 5.2, etc.... When I changed it to a numeric field with the same values, it worked fine. Sometimes just talking to someone helps. Thank you.

[ItsMe]

Glad you were able to get it sorted.