how to change am or pm based on the time entered

[Hrithika]

I have a form where I am capturing StartTime and EndTime of certain events. I have set the input mask as 99:00\ >LL;0;_

My users think it is too much to enter am or pm. What they want is the system to calculate the am pm based on the time entered.

If the time entered is between 8:00 to 11:59, the system should save it as 11:59 am
and if the time entered is between 12:00 to 7.00, the system should save it as 7.00 pm.

How can I make this happen.

Please suggest some ideas.

 

[Hrithika]

I was able move a little bit further. Given below is the code. This lets me get what I want but the database would not allow save pointing to datatype mismatch. Just need a convert function to save it in the hh:mm am/pm format. Any quick ideas?

Private Sub StartTime_Exit(Cancel As Integer)
Dim StartHour As Variant
If Len(Me.StartTime & vbNullString) > 0 Then
If DatePart("h", Me.StartTime) >= 7 _
And DatePart("h", Me.StartTime) <= 11 Then
StartHour = "Me.StartTime" & "AM"
End If
If DatePart("h", Me.StartTime) = 12 _
Or DatePart("h", Me.StartTime) < 7 Then
StartHour = "Me.StartTime" & "PM"
End If

End If
Me.StartTime = StartHour
End Sub

 

[RainLover]

Where does this fail.

Do you get a line in Yellow.

 

[Hrithika]

On exit I get a Run-time error '2147352567 (8000009)':
The value you entered isn't valid for this field.

On Debug the following line gets highlighted in yellow. However the textboxt has the value in the right format just as I wanted, but unable to proceed
Me.StartTime = StartHour

 

[Hrithika]

Alright, I got the solution I wanted. Here is what I did. Change the input mask for the text box from 99:00\ >LL;0;_ to 99:00\ >?? so that user can skip entering the AM/PM part.

Put this code in the Onexit event of the textbox

Private Sub StartTime_Exit(Cancel As Integer)
Dim StartHour As Date

'Debug.Print DatePart("h", Me.StartTime)

If Len(Me.StartTime & vbNullString) > 0 Then

If (DatePart("h", Me.StartTime) >= 7 _
And DatePart("h", Me.StartTime) <= 11) _
And Right(Me.StartTime, 2) = "AM" Then
StartHour = Format(Me.StartTime, "Short Time")
End If

If (DatePart("h", Me.StartTime) >= 7 _
And DatePart("h", Me.StartTime) <= 11) _
And Right(Me.StartTime, 2) = "PM" Then
StartHour = Format(DateAdd("h", 12, [Me.StartTime]), "Short Time")
End If

If (DatePart("h", Me.StartTime) = 12 _
Or DatePart("h", Me.StartTime) < 7) _
And Right(Me.StartTime, 2) = "PM" Then
StartHour = Format(Me.StartTime, "Short Time")
End If

'Debug.Print Right(Me.StartTime, 2)

If DatePart("h", Me.StartTime) < 7 _
And Right(Me.StartTime, 2) = "AM" Then
StartHour = Format(DateAdd("h", 12, Me.StartTime), "Short Time")
'Debug.Print Format(DateAdd("h", 12, Me.StartTime), "Short Time")
End If

End If

Me.StartTime = StartHour

End Sub



Thanks RainLover for viewing the post.

 

[RainLover]

I would have used something like

If DatePart("h", Me.StartTime) < 12 Then
    Right(Me.StartTime, 2) = "AM"
    Else: Right(Me.StartTime, 2) = "PM"
End If

This is very rough code and needs to be Debugged.

My 4 lines is better than what you have, even if it doesn't work first up.

Are you getting paid for this or are you doing some volunteer work?

I ask this because you have not put much work into the way you asked the question. Asking the correct is half way to finding the solution.