calculating all records

[hongbin81]

I have a table called PRICE with Duration, Time, and Cost

I wrote a code using Select Case method for the Cost.
This cost is related to Duration and Time.
What I need to do is calculate the Cost for all RECORDS.

So for example (My Cost field is blank right now since I can't get this part to work)

Duration Time Cost
30 6:30:32 0
30 12:45:21 0
15 11:21:11 0
45 3:12:42 0
so on...
I need to calculate all fields......I have the code ready and I can update them ONE by ONE but not all at ONCE....is it possible to use Update Query in this situation?
any help would be appreciated. Thanks

 

[kerplunkitt]

I don't quite understand the calculation method regarding Duration and the time.
However if you have a formula that works then yes you can use an update query.
The cost field would have something like the following typed into the 'Update to' box:
[Price]![Cost]=[Price]![Duration]*[Price]![Time]
This is only a guide, just to give you something to aim at!
I have not tested this since i do not know the exact field names,the calculation involved, or if there is more than 1 table involved - if there is more than 1 table then you will need to join them.

Hope this helps

Regards

IAN

 

[hongbin81]

It's not a simple formula.

The code I have is this:

Select Case Me![Time]

Case #7:00:00 AM# To #8:49:59 AM#
If Duration = 15 Then
Cost = 33
ElseIf Duration = 30 Then
Cost = 55
ElseIf Duration = 45 Then
Cost = 77
ElseIf Duration = 60 Then
Cost = 93
Else
Duration = 0
Cost = 0
End If

and so on for other various time intervals.

So I need to use this code somehow to update all the Costs given Duration and Time.

 

[Titan5]

You can create a public function like
Public Function GetCost(MyTime as date, MyDuration as integer) as currency 'The data types should be identical to table fields'.
Select Case MyTime

Case #7:00:00 AM# To #8:49:59 AM#
If MyDuration = 15 Then
GetCost = 33
ElseIf MyDuration = 30 Then
GetCost = 55
ElseIf MyDuration = 45 Then
GetCost = 77
ElseIf MyDuration = 60 Then
GetCost = 93
Else
'Duration = 0 'needs to use default value so that it will be 0 when the record is created.
GetCost = 0
End If
....
Ecd Function

Then use update query:

[Cost] - update to: GetCost([Time],[Duration])

Hope this helps.