Solved

MS ACCESS text datetime field compared with datereltoday in query

Posted on 2013-12-10
4
421 Views
Last Modified: 2013-12-10
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
Comment
Question by:ghettocounselor
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Closing Comment

by:ghettocounselor
Comment Utility
This worked as advertised.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now