date convertion (s)

kregggower 

you can also create a custom function and call it from your query:

Public Function fnToDateTime(ByVal var As Variant, ByVal whichPart As String) As Variant
' arnelgp
'
' Note:
'
' whichPart can be "D" (for date) or "T" for time
'
Static pvar As Variant
Static retDate As Date, retTime As Date
Dim d As String, t As String
If IsNull(var) Then
    Exit Function
End If
If var <> pvar Then
    d = Left$(var, 8)
    t = Mid$(var, 9)
    retDate = DateSerial(Left$(d, 4), Mid$(d, 5, 2), Right$(d, 2))
    retTime = #12:00:00 AM#
    If Len(t) <> 0 Then
        retTime = CDate(Left$(t, 2) & ":" & Mid$(t, 3, 2) & ":" & Right$(t, 2))
    End If
    pvar = var
End If
Select Case whichPart
    Case "D"
        fnToDateTime = retDate
    Case "T"
        fnToDateTime = retTime
End Select
End Function

on your Query:

select [strDateField], fnToDateTime([strDateField], "D") As Dte, fnToDateTime([strDateField], "T") As Tim 

from yourTable;

kregggower 

Thank you Karl,

I added the mid file this is the return..

Hi,

With the string functions you create a string. If you want to compare it with a date, you have to convert it to a date:

NewDate: cdate(Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4))

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon