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
Solved

Convert String to Date DDMMMYY Format

Posted on 2011-02-28
6
943 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
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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 49

Expert Comment

by:Gustav Brock
ID: 35008948
You are welcome!

/gustav
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 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