Text string to time format

[Hat Man]

How do i convert a text string to a time format.
ie. 3:15pm or 8:13am to a time format that i can run querries with a
criteria of anything later than 3PM?


Thanks

 

[Joe4]

You can use the TimeValue function to convert a text value to time.

 

[Toonies]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1, B1")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

http://www.cpearson.com/excel/datetimeentry.htm

 

[Joe4]

Toonies,

You have posted an Excel solution to an Access problem (maybe you didn't realize that you were in the Access forum!).

I also don't think it is necessary to create anything nearly that complex when an existing Access function can do the job.