Solved

Convert String to Date DDMMMYY Format

Posted on 2011-02-28
6
967 Views
Last Modified: 2012-05-11
I have a field with strings like:
"20 ML @ 26FEB11 1541"
"34 GM @ 26FEB11 1541"
"1 MG @ 26FEB11 1541"

which I need to query on to get the records with the last 36 hours. What's the best way to extract the date/time so I can apply the WHERE clause: Between DateAdd("h",-36,Now()) And Now()
Thanks!

MV
0
Comment
Question by:Michael Vasilevsky
[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
6 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 35003818
Create a separate field of the type "datetime" (or "date") - make sure that you index it.

It might be easier if you do this parsing in VBA (as opposed to doing it in SQL).

Then run the SQL on that field.

sample code - please debug and test

dim dbs as database
dim rst as recordset
dim str1 as string

set dbs = currentdb
set rst = dbs.openrecordset("select stringdate, datedate from table") 'stringdate is the existing field, datedate is new date type date
if rst.recordcount > 0 then
  rst.movefirst
  with rst
    do while not .eof
      str2 = ''
      str1 = rst!stringdate 'we get 34 GM @ 26FEB11 1541"
      str1 = mid(str1, instr(1, str1, "@ ")+2) 'we get 26FEB11 1541
      select case mid(str1,3,3)
      case "JAN"
        str2 = "01/"
      case "FEB"
        str2 = "02/"
      .......and so on till "DEC"
      end case
      str2 = str2 & mid(str1, 1, 2) & "/20" & mid(str1, 6, 5) & ":" & right(str1, 2) & ":00" 'mm/dd/yyyy hh:mm:00
      .edit
        rst!datedate = str2
      .update
      .movenext
    loop
  end with
end if
set rst = nothing
set dbs = nothing

Ss
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35004453
I agree with 'sshah254' that a VBA function is a good thing to consider.  Combining your request with the VBA function, I am suggesting the following function:

Function L36H(FS As String) As Boolean

    Dim P As Date
    Dim intA, intS As Integer
    Dim strM, strT, strYY, strMMM, strDD As String
       
    intA = InStr(1, FS, "@") + 2                    'Locate the @ sign + 2
    intS = InStr(12, FS, " ")                           'Locate the space after the date
    strM = " " & Mid(FS, intA, intS - intA)      'Extract the Date (Pad with 1 space in case of 1-digit day)
    strT = Left(Right(FS, 4), 2) & ":" & Right(FS, 2)   'Extract the Time as hh:mm
    strYY = Right(strM, 2)                              'Extract the 2-digit Year
    strMMM = Left(Right(strM, 5), 3)               'Extract the 3-char Month
    strDD = Left(Right(strM, 7), 2)                  'Extract the 2-digit Day
   
        'Convert to Date & Time
   P = Format(strDD & " " & strMMM & " " & strYY & " " & strT, "dd MMM yy hh:mm")

   If Now() - P <= 1.5 Then                            'Test if in the last 36 hours
        L36H = True
    Else
        L36H = False
    End If
   
End Function

In your query, add a column to test the field and test for TRUE.  i.e.  Test:  L36H([fieldname]) with the Criteria = TRUE

John
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 35006176
Just extract the date/time value from that field using Format and CDate:

DateTime: CDate(Format(Right([YourCode@DateTimeField],12),"!@@ @@@ @@@@@:@@"))

Then apply your filter to this column.

/gustav
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 35008410
Thanks all I knew I could parse the data with a VBA function and force a date but gustav's is the elegant solution I was looking for.
Thanks!

MV
0
 
LVL 10

Author Closing Comment

by:Michael Vasilevsky
ID: 35008414
perfect
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 35008948
You are welcome!

/gustav
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

689 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