Time Intervals

[hongbin81]

QUESTION:

When I have a Time Field in tblLogs and if the Time ranges from 1:00:00 PM to 2:00:00 PM then I want to put for example a 30 in a different field called "Spot Rate". The Time can Range from 12:00:00 to 1:00:00, 1:00:00 to 2:00:00, 2:00:00 to 3:00:00 and so on.

What do I do?

I was thinking using the Select Case Method but I couldn't get it working.

 

[epoirier]

yes you can use Select Case

select case me![TimeField]

case 02:00:00 to 2:59:59
SpotRate = 1
case 03:00:00 to 3:59:59
SpotRate = 2
case etc...


End Select

 

[hongbin81]

I tried that way and I get an error and its highlighted on the double zero after the 02:
Something about

Expected: Line Number or Statement or Label or End of Statement

????

 

[hongbin81]

Nevermind, I think it works somewhat. Just need to fix something here and there.

Merci

 

[hongbin81]

PROBLEM:

This only worked for ONE Listing. I got multiple listings with multiple times in the Time Field. By doing what I just did using Select Case Method, it only updates the Spot Rate one at a time. How do I do it so it'll update all at ONCE?

 

[epoirier]

I need more information of your structure of your data

but You can't try to make a function to return Rate of each List

Exemple:

Private sub Commande1()

Dim iReturnRate As Integer
Dim a As Date
a = time 'System Time for Test.

iReturnRate = MyRate(a)
MsgBox iReturnRate

End Sub
Private Function MyRate(dtTimeField As Date) As Integer

Select Case dtTimeField
Case #2:00:00 PM# To #2:59:59 PM#
MyRate = 1
Case #3:00:00 PM# To #3:59:59 PM#
MyRate = 2
End Select

End Function

 

[hongbin81]

I have a table called "tblLogs"
It has List, Date, Time, SpotRate fields.
There are over 100 listings.
I made a form simply using the wizard that lists the listings, date, time and SpotRate.
What I want to do is for the Times for each listings, put a number onto the Spot Rate according to the the Time Ranges as stated above. I made a Command Button with the code specified above. It only updates the Spot Rate if it is focused on ONE record. Problem is, I want it with ONE click of a button to Update all the Spot Rates with designated numbers without hilighting through ONE List at a Time.

 

[epoirier]

You can open table with DAO and pass on each record in your table.

Other solution, maybe you can do this in Query update.


Private Sub Commande0_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

'Open your table tblLogs.
Set rst = db.OpenRecordset("tblLogs", dbOpenDynaset)

With rst
If .RecordCount Then
Do While Not .EOF

.Edit
'Your field SpotRate and Time.
![SpotRate] = MyRate(![Time])
.Update
.MoveNext
Loop
End If
.Close: Set rst = Nothing
End With

db.Close: Set db = Nothing
End Sub

Private Function MyRate(dtTimeField As Date) As Integer

Select Case dtTimeField
Case #2:00:00 PM# To #2:59:59 PM#
MyRate = 1
Case #3:00:00 PM# To #3:59:59 PM#
MyRate = 2
End Select

End Function

 

[hongbin81]

hmm..for some reason, I can't work DAO, but ADODB works.