asked on

MS ACCESS text datetime field compared with datereltoday in query

I have a text field called CUSTOM_FIELD with date time data: 201312092304
And I'd like to do a where statement against this but will need to convert/format the data as a proper date in the process; something like this eventually once formated ((PHM_ENHANCED_CHGS.CUSTOM_FIELD)=DATERELTODAY(-1)))

Dale Fye

I cannot imagine why someone would create a string to represent a date! Well, actually I can imagine it because I run into it all the time. But I don't know why they would want to do so.

So, rather than try to create a complicated string of Lefts, mids, rights, ..., I would recommend you write a function to do the conversion for you, then call the function and pass it the value of your Custom_Field. An example might look like:

Public Function ConvDate(DateStr As Variant) As Variant

    If IsNull(DateStr) Then
        ConvDate = Null
        Exit Function
    End If
    
    Dim lngYear As Long
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim intHour As Integer
    Dim intMinute As Integer
    
    lngYear = Val(left(DateStr, 4))
    intMonth = Val(Mid(DateStr, 5, 2))
    intDay = Val(Mid(DateStr, 7, 2))
    intHour = Val(Mid(DateStr, 9, 2))
    intMinute = Val(Mid(DateStr, 11, 2))
    
    ConvDate = DateSerial(lngYear, intMonth, intDay) _
             + TimeSerial(intHour, intMinute, 0)
    
End Function

Open in new window

and your where clause might look like:

WHERE ConvDate(PHM_ENHANCED_CHGS.CUSTOM_FIELD)=DATERELTODAY(-1)

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Yeah, that would work too Miriam. Why do I always seem to look for the function when a simple formatting would work? ;-)

Although the formatting of the [CUSTOM_FIELD] appears to include HHMM as well, so the OP might need to try:

PHM_ENHANCED_CHGS.CUSTOM_FIELD = Format(DATERELTODAY(-1),"YYYYMMDDHHNN")

if the hour and minute are critical.

ghettocounselor

ASKER

This worked as advertised.