Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1028
  • Last Modified:

Convert String to Date DDMMMYY Format

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
Michael Vasilevsky
Asked:
Michael Vasilevsky
1 Solution
 
sshah254Commented:
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
 
JAMcDoCommented:
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
 
Gustav BrockCIOCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Michael VasilevskySolutions ArchitectAuthor Commented:
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
 
Michael VasilevskySolutions ArchitectAuthor Commented:
perfect
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now