Time comparrison

[napiedra]

I have the script below that it is supposed to give prices based on Datediff between TimeIn and TimeOut. The clients can pay when they comeback for the actual time elapsed. In increments of 20 min they have to pay 2 dollas. Ex. If time elapsed is from 1 to 20 is 2 dollars and from 21 to 40 $4.00 etc. Once the user goes over $12.00 there is not more changes because 12 is the maximum for the day. Now, there has been a change that requires that after 11:00 AM the maximum is change to $7.00 so in the following script I have to, somehow, add that if TimeIn is bigger than 11:00 AM then X = 7. I started to do that but I am not sure how to do it as the time is not by itself but it has the date in front that comes from the built-in function Date()

Any help will be appreciated, here is the code:

Private Sub FindTAG_AfterUpdate()



If Me.FindTAG & vbNullString <> vbNullString Then
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[TAG]=" & Me.FindTAG
If rs.NoMatch Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me!TAG = Me.FindTAG
Me.TimeIn = Now()
Me.HourlyRate = 6
MsgBox "Ticket Added, Click 'OK' To Continue!"
DoCmd.GoToRecord acDataForm, "CTPLLC", acNewRec
Forms!CTPLLC!FindTAG.SetFocus
Else
Me.Bookmark = rs.Bookmark
If IsNull(Me.TimeOut) Then
Me.TimeOut = Now()
Me.ElapsedTime = DateDiff("n", [TimeIn], [TimeOut])


'If Me.TimeIn > Date()#11:00 AM# Then x = 7
Select Case [ElapsedTime]
Case 1 To 20
x = 2
Case 21 To 40
x = 4
Case 41 To 60
x = 6
Case 61 To 80
x = 8
Case 81 To 100
x = 10
Case 101 To 660
x = 12
Case 660 To 845
x = 17
End Select
If [ElapsedTime] > 17 Then
MsgBox "WARNING!!! Time is over 11 hours!"
Else
Me.AmountOwed = x
If Me.Dirty Then
Me.Dirty = False
End If
End If



Else
MsgBox "NOTICE:This is a previously Used Ticket!"
Forms!CTPLLC!Next.SetFocus
End If
End If
rs.Close
FindTAG = Null
End If
End Sub


Private Sub Next_Click()
On Error GoTo Err_Next_Click
DoCmd.GoToRecord acDataForm, "CTPLLC", acNewRec
Forms!CTPLLC!FindTAG.SetFocus
Exit_Next_Click:
Exit Sub
Err_Next_Click:
MsgBox "Sorry, There Is Missing Information!"
Resume Exit_Next_Click
End Sub

[ssanfu]

I haven't tested this, but it should work.
The lines I added are in blue.

(Cleaned up a couple of other things....)

Option Compare Database   '<-- should be at the top of every module
Option Explicit                            '<-- should be at the top of every module

Private Sub FindTAG_AfterUpdate()
   Const conTimeLimit As Date = #11:11:00 AM#
   Const conMaxAmt As Integer = 7

   Dim rs As DAO.Recordset
   Dim x As Integer

   x=0
   
   If Me.FindTAG & vbNullString <> vbNullString Then
      Set rs = Me.RecordsetClone
      rs.FindFirst "[TAG]=" & Me.FindTAG
      If rs.NoMatch Then
         DoCmd.GoToRecord acActiveDataObject, , acNewRec
         Me!Tag = Me.FindTAG
         Me.TimeIn = Now()
         Me.HourlyRate = 6
         MsgBox "Ticket Added, Click 'OK' To Continue!"
         DoCmd.GoToRecord acDataForm, "CTPLLC", acNewRec
         Forms!CTPLLC!FindTAG.SetFocus
      Else
         Me.Bookmark = rs.Bookmark
         If IsNull(Me.TimeOut) Then
            Me.TimeOut = Now()
            Me.ElapsedTime = DateDiff("n", Me.TimeIn, Me.TimeOut)
            'If Me.TimeIn > Date()#11:00 AM# Then x = 7
            Select Case Me.ElapsedTime
               Case 1 To 20
                  x = 2
               Case 21 To 40
                  x = 4
               Case 41 To 60
                  x = 6
               Case 61 To 80
                  x = 8
               Case 81 To 100
                  x = 10
               Case 101 To 660
                  x = 12
               Case 660 To 845
                  x = 17
            End Select
            If Me.ElapsedTime > 17 Then
               MsgBox "WARNING!!! Time is over 11 hours!"
            Else
               'check for max time
               If Me.TimeOut > conTimeLimit Then
                  Me.AmountOwed = conMaxAmt
               Else
                  Me.AmountOwed = x
               End If
               
               If Me.Dirty Then
                  Me.Dirty = False
               End If
            End If
         Else
            MsgBox "NOTICE:This is a previously Used Ticket!"
            Forms!CTPLLC!Next.SetFocus
         End If
      End If
      rs.Close
      Set rs = Nothing

      Me.FindTAG = Null
   End If
End Sub

[napiedra]

After testing this and playing with it two things I noticed. First, after 11:00 all incoming were reporting 7 dollars which it was not my intention. I needed a field for those who decide to pay the maximum and if they don't the rate it is still calculated in 20 min increments. If they do, then the rate should be posted in the database and at the end when the user comes back if they did not go over the maximum, we have to refund the difference.

For example,

A client comes at 8:00 and decides to pay by the hour after he comes back. This client after 1 hour and 40 min already consumed $12 but if this client comes back after 40 min then he only needs to pay $4.

Another client comes in at 8:00 and decides to pay full day $12, if this client returns in 40 min we have to refund $8.

After 11:00 AM if a client comes in he can also decide to pay by the 20 min increment and maybe comes back in 1 hour and only has to pay $6. Another client comes after 11:00 AM and decides to pay full amount (which after 11:00 AM is only $7) this person can also come back in 40 min and we have to refund $2 etc.

The same 20 min increment applies to all times except for those who decide to pay full amount of $12 before 11:00 AM or $7 after 11:00 AM.

I am attaching what I have so far. I was also thinking about making a refund field on the fly and not add it to the table but I am not sure what is the best choice. Please, review if I am on the wrong path or not.

[napiedra]

Also, I was trying to add a popup box that the user can click Yes or Not. When a client comes in, the user scans a new TAG and the pup up ask "Is this client paying full amount?" if the user click Yes, the PaidAmount is $12 before 11:00 AM and if Yes is clicked after 11:00 AM the PaidAmount is $7. If the user clicks No, then the PaidAmount is $0.00 at any time.