Updating using a Function 1

[hongbin81]

I have a function...

fGetCost([Date],[Time],[Duration])

This function results in indicating a Cost according to Date, Time and Duration.

I need to update ALL RECORDS using this function.

Is there a way of applying this function using update query to update all records in my table?

 

[GingerR]

yes.

have you tried writing an update query already, with the function in it? what have you tried so far? what's not working for you?

 

[hongbin81]

I'm not sure how to do it with a function.
I tried putting in fGetCost([Date],[Time],[Duration]) in a field, but that only updated one record, not all.

 

[mikevh]

Sure.

UPDATE YourTable
SET FieldYouWantToUpdate=fGetCost([Date],[Time],[Duration]);

where YourTable is the name of your table
and FieldYouWantToUpdate is the field you want to update.

You should run a SELECT query and/or back up
your table first, just to make sure the query really does
what you want it to do, e.g.,

SELECT FieldYouWantToUpdate, [Date],[Time],[Duration], fGetCost([Date],[Time],[Duration]) AS NewVal
FROM YourTable;

 

[hongbin81]

The function I wrote is in Module.
So do I have to direct which Module I wrote the function in order to use the function in SQL?

 

[GingerR]

start a new query.
from menu select VIEW+SQL
copy the last sentence from Mikevh's post
paste it in the sql window
tweak with your field and yourTable names
run the query

perhaps your query only updates one record because you have some criteria in it that limits the number of records? does your query run as you expect if you take out the function call, and change it to a select query? or does it just return one record?

 

[hongbin81]

I don't have any criteria that limits the number of records. This is my function. There's nothing wrong is there?

Function fGetCost(iDate As Date, iTime As Date, iDuration As Integer) As Double

Dim Week As Integer
Week = Weekday([iDate], 2)

If (Week = 6) Or (Week = 7) Then

Select Case iTime
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

so on for various time intervals...

I have a tblRate which as the following Fields.
List, Date, Time, Duration, Cost.

From tblRate's Date, Time, and Duration, I have to use that function to update all the Costs in that Table.

I used the methods shown above....and I can't seem to get it to work. -.-;;

 

[mikevh]

May we see the rest of the function? You haven't
posted all the code.

 

[hongbin81]

Function fGetCost(iDate As Date, iTime As Date, iDuration As Integer) As Double

Dim Week As Integer
Week = Weekday([iDate], 2)

If (Week = 6) Or (Week = 7) Then

Select Case iTime
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

(so on for various time intervals...)
End Select

Else
Select Case iTime
Case #7:00:00 AM# To #8:59:59 AM#
If iDuration = 15 Then
SpotRate = 33

ElseIf iDuration = 30 Then
SpotRate = 55

ElseIf iDuration = 45 Then
SpotRate = 77

ElseIf iDuration = 60 Then
SpotRate = 93

Else
iDuration = 0
SpotRate = 0
End If

(so on for other time intervals)..
End Select
End If

End Function

 

[hongbin81]

the SpotRate's should be "Cost"

 

[mikevh]

I don't see anywhere in the function that you're assigning
a value to the function itself. This is how VB functions
return values, by assigning a value to the function name.
Without this, the function returns no value. (This may be
the major reason this isn't working.)

Just before the "End Function", say "fGetCost = Cost".
This will cause the function to return a value.

Another point (which may be something of a personal preference): You're changing the value of one of your
input params. In the final "Else" of each of your "If"'s,
you say "iDuration = 0". Since argument passing is by
Reference in VB by default, this will actually change the
value of iDuration back in any routine that calls this one.
It really shouldn't have any effect here, but it's rather
dangerous, I think. I recommend passing arguments to
functions by Value, i.e.,

Function fGetCost(ByVal iDate As Date, ByVal iTime As Date, ByVal iDuration As Integer) As Double

This way if you accidentally make a change to one of your
params in a function, you're only changing a local copy
and the calling routine's values aren't affected.

And one last thing. In one of your earlier posts, you said:
"I tried putting in fGetCost([Date],[Time],[Duration]) in a field, but that only updated one record, not all."
Can you explain what you meant by "you tried putting it in
a field"?

 

[hongbin81]

ahh~~ that was the problem. NO RETURN!
Thank You so MUCH!
^_________________^

 

[mikevh]

Great. Is it worth a star?

 

[hongbin81]

heh..I didn't even know you can give a "STAR".
^_^
Thanks!