calculating ALL records at once

[hongbin81]

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.

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

 

[oharab]

I would create a new function that uses what you've written so far:
Function fGetCost(dtime as date, iduration as integer) as currency
The code I have is this:

Select Case dtime

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

and so on for other various time intervals.

End Function.

You can then use this in your query as you would any other function.

hth

Ben


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[hongbin81]

I've been using access for a little over a month now.
So the function made would be used in update query?
How is a function used in query?

 

[oharab]

Create your query as normal, adding in the fields you want to see, then in the next column put fGetCost([Time],[Duration]) then when you run the query access will pass the fields time & duration through the function for each row in the query.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[hongbin81]

I'll give it a try now.

 

[hongbin81]

Thank you!

It worked nicely!

 

[hongbin81]

Question:

Why does the Expression fGetCost([Time],[Duration]) dissapear everytime i close the query?

 

[hongbin81]

Actually it didn't work!

It only updated ONE record. Not ALL.

 

[oharab]

If you run this query then it should update all the cost fields in tblCost, you will need to change tblCost for whatever your table is called.

UPDATE tblCost SET tblCost.Cost = fgetcost(tblCost.Time,tblCost.Duration);

if you only want to update some of them then ie where the client is called RobotParade then modify your SQL statement to:
UPDATE tblCost SET tblCost.Cost = fgetcost(tblCost.Time,tblCost.Duration) WHERE tblCost.Client='RobotParade';


hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------