Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

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)))
0
ghettocounselor
Asked:
ghettocounselor
  • 2
1 Solution
 
Dale FyeCommented:
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)
0
 
mbizupCommented:
You could take the reverse approach and format the DATERELTODAY:

LEFT(PHM_ENHANCED_CHGS.CUSTOM_FIELD,8) = Format(DATERELTODAY(-1),"YYYYMMDD")

Open in new window

(assuming that DATERELTODAY(-1) gives results in a date format)

Alternatively:


LEFT(PHM_ENHANCED_CHGS.CUSTOM_FIELD,8) = Format(Date() - 1,"YYYYMMDD")

Open in new window

0
 
Dale FyeCommented:
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.
0
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
This worked as advertised.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now