Hi all,
I am trying to update a field in a table with calculated values, dependent on the values from another table which can change every so often.
Here's the scenario. TblTripCosts contains many fields with numeric values, but for this example let's say it only has three. [Destination], [AmboLSCharge] and [Kilometres]
I am trying to update the table's [AmboLSCharge] field by combining the results of looking up two other fields in another table, and multiplying one of those results by the kilometres in TblTripCosts.
The other table, TblDataTypes, has two fields called [DataType] and [Value].
Example:
TblTripCosts
Destination = Dubbo
AmboLSCharge =
Kilometres = 45
TblDataTypes
DataType = AmboLSFlagFall, Value = 617.00
DataType = AmboLSKms, Value = 1.77
So, what I want the function to do is to update all entries in TblTripCosts to reflect the AmboLSCharge to equate to [TblDataTypes].[AmboLSFlagFall] + ([TblDataTypes].[AmboLSKms] * [TbltripCosts].[Kilometres])
The code I tried in the function is not good enough, as it wipes the field completely! haha
Can someone point me in the right direction please?
Code:
Public Function TripCoster()
Dim ALSFF, ALSKM As Integer
ALSFF = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSFlagFall'")
ALSKM = DLookup("Value", "TblDataTypes", "Datatype = 'AmboLSKms'")
With TblTripCosts
.AmboLSCharge = ALSFF + (ALSKM * [TblTripCosts].[KILOMETRES])
End With
End Function
thanks in anticipation
Pete