trim date from text field

lookforsmt

Registered User.
Local time
Today, 11:39
Joined
Dec 26, 2011
Messages
672
HI!

I have db with table which has text as mentioned below. I am not able to separate the date from the text which has other characters.
I have done with partial success for months with single digit(1 to 9) but when it is two digit (10 to 12) the query does not give me the desired result.
can anyone help me to get the result I want.

Logged Error -8/1/2016 3:41:45 PM-fmo07397
Logged Error -8/9/2016 3:41:45 PM-fmo07397
Logged Error -8/10/2016 3:41:45 PM-fmo07397
Logged Error -8/11/2016 3:41:45 PM-fmo07397
Logged Error -8/19/2016 3:41:45 PM-fmo07397
Logged Error -8/20/2016 3:41:45 PM-fmo07397
Logged Error -8/30/2016 3:41:45 PM-fmo07397
Logged Error -8/31/2016 3:41:45 PM-fmo07397
Logged Error -10/1/2016 3:41:45 PM-fmo07397
Logged Error -10/9/2016 3:41:45 PM-fmo07397
Logged Error -10/10/2016 3:41:45 PM-fmo07397
Logged Error -10/19/2016 3:41:45 PM-fmo07397
Logged Error -10/20/2016 3:41:45 PM-fmo07397
Logged Error -10/30/2016 3:41:45 PM-fmo07397
Logged Error -10/31/2016 3:41:45 PM-fmo07397

Expected result

01/08/2016
31/10/2016

many thanks
 

Attachments

  • qry_errordate1.jpg
    qry_errordate1.jpg
    104.3 KB · Views: 133
  • qry_errordate2.jpg
    qry_errordate2.jpg
    94.8 KB · Views: 116
create a public function in a module and call it within your select/update query:

Code:
Public Function fnGetDatePart(vfield As Variant) As Variant
Dim v As Variant
vfield = vfield & ""
v = Split(vfield, "-")
On Error Resume Next
fnGetDatePart = DateValue(CDate(v(1)))
End Function
on your query:

select datefield, fnGetDatePart([datefield]) As LogDate from yourTable;
 
Thanks for the reply with solution. I am getting the date but there is still one problem. the date is showing in some rows as mm/dd/yyyy and some rows as dd/mm/yyyy

I have attached db. kindly pls help me

thanks
 

Attachments

hi anyone can help me, the date can be either of the formats mm/dd/yyyy or dd/mm/yyyy but it should not be both.
I tried my luck in all possible solutions on this forum but I am not able to get the desired result.
 
I'm surprised you got any date at all. When I tried the query I got "function not found" which was happening because the module had the same name as the function. I gave the module a different name in the attached database and also added a format to mr. arnelgp's function. The change is shown in red below

Code:
Public Function fnGetDatePart(vfield As Variant) As Variant

Dim v As Variant
vfield = vfield & ""
v = Split(vfield, "-")
On Error Resume Next
fnGetDatePart = [COLOR="Red"]Format([/COLOR]DateValue(CDate(v(1)))[COLOR="red"], "mm/dd/yyyy")[/COLOR]

End Function

This should give you the dates in mm/dd/yyyy format if you want dd/mm/yyyy just change it to that in the format function.
 

Attachments

thanks for your reponse. but my issue here is the date shows both format dd/mm/yyyy & mm/dd/yyyy in the rows.
is there a way to get all the rows with dd/mm/yyyy only or mm/dd/yyyy only either way

Since when I filter the data on report the the days will be mistaken for month
 
thanks for your reponse. but my issue here is the date shows both format dd/mm/yyyy & mm/dd/yyyy in the rows.

Where are you see that? I'm not getting that in the Query1 results. Please post a screen shot of what you are seeing.
 
I have attached the snap shot of the query1 which you have provided. I need the column to be in dd/mm/yy format only
 

Attachments

  • query1.jpg
    query1.jpg
    105.7 KB · Views: 128
Ok Thank for the screen shot. It's not doing that on my system probably because my dates are set up as mm/dd/yyyy. I'm looking for a fix.
 
I can't think of anyway to fix Mr. arnelgp's function. Maybe he can. In the meantime I've creating a less sophisticated function that should work as long as the date is preceded by a dash and ended with a space. The function is

Code:
Public Function GetDatePart(fd As Variant) As Variant
'this function returns the string within fd between the first dash found and
'the next space found after that dash
If IsNull(fd) Then
    GetDatePart = Null
    Exit Function
End If
Dim FirstDashPostion As Long
Dim SpaceAfterDatePosition As Long
FirstDashPostion = InStr(1, fd, "-")
SpaceAfterDatePosition = InStr(FirstDashPostion, fd, " ")
GetDatePart = Mid(fd, FirstDashPostion + 1, SpaceAfterDatePosition - FirstDashPostion)

End Function

You can find this in the attached database. Please note that this function returns a string not a date type.
 

Attachments

Hi! thanks for the solution, but it still does not help as the format gives a different result.

I am not sure whether there is any way to get the desired result.
What you provided the solution was really amazing and good but unfortunate it did not help me.

thanks
 
I believe the function extracts the date from the string as you had originally requested. Doesn't it? What do you mean by "the format gives a different result". What do you need? I'm fairly certain it can be done if you can explain it.
 
I haven't looked at the code but cdate converts a string to a date. If the string is ambiguous in terms of us/uk format then us will be the default

so suggest try

fnGetDatePart = CDate(format(v(1),"mm/dd/yyyy"))

or just within the query

LogDate: CDate(Format(Trim(Mid([result],InStr([result],"-")+1,InStrRev([result],"-")-InStr([result],"-")-1)),"mm/dd/yyyy"))
 
Last edited:
I haven't looked at the code but cdate converts a string to a date. If the string is ambiguous in terms of us/uk format then us will be the default

The result the OP is getting because of CDate can be seen in the attachment in post # 8 where the date is being converted from an american format to an uk format wherever the date could be reformatted that way. This is the problem.
 
The attached database has the following function that produces an output in uk format. Does this help any?

Code:
Public Function GetDatePart(fd As Variant) As Variant
'this function returns the string within fd between the first dash found and
'the next space found after that dash
Dim DatePart As String
Dim v As Variant
If IsNull(fd) Then
    GetDatePart = Null
    Exit Function
End If
Dim FirstDashPostion As Long
Dim SpaceAfterDatePosition As Long
FirstDashPostion = InStr(1, fd, "-")
SpaceAfterDatePosition = InStr(FirstDashPostion, fd, " ")
DatePart = Mid(fd, FirstDashPostion + 1, SpaceAfterDatePosition - FirstDashPostion)
v = Split(DatePart, "/")
GetDatePart = Format(v(1), "00") & "/" & Format(v(0), "00") & "/" & v(2)

End Function

I'll try to simplify this when we get something that works for you.
 

Attachments

The result the OP is getting because of CDate can be seen in the attachment in post # 8 where the date is being converted from an american format to an uk format wherever the date could be reformatted that way. This is the problem.
agreed - and the query solution I provided resolves that.

attachment.php
 

Attachments

  • Capture.jpg
    Capture.jpg
    78.5 KB · Views: 302
Here is another option that I found on the web which uses regular expressions for cases where you would like to extract a date and the date is not in a fixed position or the format of date is slightly different. See image for:
Example 1, no dashes
Example 2 date has dashes
Example 3 dashes before/after date and date has dashes
Example 4 mix of slash and dash in date



To use it, would may need to add a reference to Microsoft VBScript Regular Expressions, although the file I downloaded from this post seemed to work without it. (Late binding?)

I added the formatting code from sneuberg to show date as dd/mm/yyyy

Code:
Function ExtractDate(DateText)
'Extract date from a text string
'http://stackoverflow.com/questions/7929205/vba-regular-expression-to-match-date
'Added dd/mm/yyyy format 
'http://www.access-programmers.co.uk/forums/newreply.php?do=postreply&t=288808
    Dim re, match
    Dim v as Variant   
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "[\d]+[\/-][\d]+[\/-][\d]+"
    re.Global = True

    For Each match In re.Execute(DateText)
        If IsDate(match.Value) Then
            ExtractDate = CDate(match.Value)
            
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Comment this part if using mm/dd/yyyy format
            v = Split(ExtractDate, "/")
            ExtractDate = Format(v(1), "00") & "/" & Format(v(0), "00") & "/" & v(2)
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            Exit For
        End If
    Next
    Set re = Nothing

End Function
 

Attachments

  • ExtractDateExample.PNG
    ExtractDateExample.PNG
    7.4 KB · Views: 133
Last edited:
First I want to apologize for not responding as I was away from my laptop for sometime.

Yes thanks for the solution. It has finally worked and thanks to each and everyone who have made feel happy.

I will close this thread with stats solved

thanks
 
Please note that CJ London's much simpler solution

Code:
LogDate: CDate(Format(Trim(Mid([result],InStr([result],"-")+1,InStrRev([result],"-")-InStr([result],"-")-1)),"mm/dd/yyyy"))

seems to work fine too. I don't understand how Format tells CDate "this is US format but apparently it does somehow.
 
Thanks I have tried all the solutions and all of them fit as my requirement. I once again thank you all good people for helping me in achieving my goal.

thanks
 

Users who are viewing this thread

Back
Top Bottom