Referring to query field in Function 1

[londonkiwi]

I'm trying to refer to fields within query "qryCost" to evaluate the value of ETC. I have a field in qryCost ExpectedTC: ETC(). However when I run the query all it returns in the EpectedTC field is 0.

Would appreciate any advice. Do I use DLookup... and if so how. cheers

Function ETC()

Dim TenderPrice As Double ' field in query
Dim Cost As Double ' field in query
Dim Budget As Double ' field in query

ETC = 0

If IsNull(TenderPrice) Or TenderPrice = 0 Then ' no tender price available
If Cost > Budget Then ' costs exceed budget
ETC = Cost
Else
ETC = Budget
End If
ElseIf TenderPrice > Budget Then
If Cost > TenderPrice Then ' costs > tender
ETC = Cost
Else
ETC = TenderPrice ' tender > costs
End If
ElseIf TenderPrice < Budget Then
If Cost > TenderPrice Then ' costs > tender
ETC = Cost
Else
ETC = TenderPrice '
End If
End If
End Function

 

[tunsarod]

Try this:

In the query field the expression should read:-
ExpectedTC: ETC([TenderPrice],[Cost],Budget])

In the Function decalre the variables within the function brackets.

Function ETC(TenderPrice As Double, Cost As Double, Budget As Double) As Double

 

[londonkiwi]

Hmm... thanks for the help.

Now I get the result #Error in the ExpectedTC field.

How do I trap the error - or trace it??

cheers

 

[tunsarod]

If you copied my example faithfully then that could be the problem. I missed out the left square bracket before Budget in the query field expression. Should read:

ExpectedTC: ETC([TenderPrice],[Cost],[Budget])

also you might like to try this alternative iif/then process:

If Not TenderPrice > 0 Then
ETC = iif(Cost > Budget , Cost, Budget)
ElseIf TenderPrice > Budget Then
ETC = iif(Cost > TenderPrice, Cost, TenderPrice)
ElseIf TenderPrice < Budget Then
ETC = iif(Cost > TenderPrice, Cost, TenderPrice)
End If

I have just tried this out and it all works fine.

 

[londonkiwi]

Thanks,

I had spotted the brackets that were missed and corrected.

Still get #Error in ExpectedTC field of the qry when running it. All values are of type currency. Any ideas.
Anyway, thanks for all you help.

 

[londonkiwi]

Ok - The problem here was that some values in the field TenderPrice were indeed null. However the type double does not accept null values. Wrote a very quick SQL Update query to fill each TenderPrice field (row) with a 0. Solved the problem.