Solved

MS ACCESS text datetime field compared with datereltoday in query

Posted on 2013-12-10
4
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39708784
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
ID: 39708797
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)
ID: 39708959
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
ID: 39710043
This worked as advertised.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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