Me! - use in code

[londonkiwi]

I have three fields in a form Budget, CostToDate, and TenderPrice, and one field "EstTotalCst" I am trying to "calculate" from these. Fields refer to fields in table tblCost. My If-Then-Else Statment placed in the AfterUpdate event of "EstTotalCst" is failing miserably. Code syntax etc help please...


Private Sub ETC_AfterUpdate

If IsNull(Me!TenderPrice) Then ' no tender price available
If Me!CostToDate > Me!Budget Then ' costs exceed budget
Me!EstTotalCst = Me!CostToDate
Else Me!EstTotalCst = Me!Budget
End If

Else ' TenderPrice field is not null
End If

If Me!TenderPrice > Me!Budget Then
If Me!CostToDate> Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else Me!EstTotalCst = Me!TenderPrice ' tender > costs
End If

Else 'TenderPrice < Budget
End If

If Me! Tender Price < Me!Budget Then
If Me!CostToDate> Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else Me!EstTotalCst = Me!TenderPrice '
End If

Else ' what??
End If

End Sub

 

[Terpsfan]

Your culprit seems to be here in your last if statement. Me! Tender Price should be Me!TenderPrice

If Me! Tender Price < Me!Budget Then
If Me!CostToDate> Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else Me!EstTotalCst = Me!TenderPrice '
End If

 

[londonkiwi]

Thanks

Are there any easier ways to do this - Select Case statement appropriate??

cheers

 

[BillPower]

Private Sub ETC_AfterUpdate()
If IsNull(Me!TenderPrice) Then ' no tender price available
If Me!CostToDate > Me!Budget Then ' costs exceed budget
Me!EstTotalCst = Me!CostToDate
Else
Me!EstTotalCst = Me!Budget
End If
ElseIf Me!TenderPrice > Me!Budget Then
If Me!CostToDate > Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else
Me!EstTotalCst = Me!TenderPrice ' tender > costs
End If
ElseIf Me!TenderPrice < Me!Budget Then
If Me!CostToDate > Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else
Me!EstTotalCst = Me!TenderPrice '
End If
End If
End Sub

 

[londonkiwi]

Works a charm.
Thanks all

 

[Terpsfan]

Select Case Me!TenderPrice
Case Is = Null ' no tender price available
If Me!CostToDate > Me!Budget Then ' costs exceed budget
Me!EstTotalCst = Me!CostToDate
Else: Me!EstTotalCst = Me!Budget
End If

Case Is > Me!Budget
If Me!CostToDate > Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else: Me!EstTotalCst = Me!TenderPrice ' tender > costs
End If

Case Is < Me!Budget
If Me!CostToDate > Me!TenderPrice Then ' costs > tender
Me!EstTotalCst = Me!CostToDate
Else: Me!EstTotalCst = Me!TenderPrice '
End If

End Select