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

x
?
Solved

Convert String to Date DDMMMYY Format

Posted on 2011-02-28
6
Medium Priority
?
1,007 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 52

Accepted Solution

by:
Gustav Brock earned 2000 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
Independent Software Vendors: 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 11

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 11

Author Closing Comment

by:Michael Vasilevsky
ID: 35008414
perfect
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35008948
You are welcome!

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

572 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