Extracting date from string field

[BMKanun]

Hi,
I have a table for planing upcoming events.
Some of my colleagues were entering date together with text in text field.
There is no patern of the date possition.
How can I extract (find) the date in the string field?

regards,

 

[Andrzejek]

Could you show some samples of your data so we know what you deal with?
Functions like [tt]Split()[/tt] and [tt]IsDate()[/tt] may come handy...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

If you have a need to extract a date value from a text field, you have a need to create a date field in your table.

Duane
Hook'D on Access
MS Access MVP

 

[BMKanun]

Hi Andy, thanks for your reply.
Here is a simulation of the table:

ID;Client;Event
1;Client1;press-conference 01.04.2015
2;Client2;20.03.2015, annyversary celebration
3;Client3;wedding party, 23.05.2015 at Palace Hotel


The only thing that is constant is the date format: dd.mm.yyyy

regards,

 

[BMKanun]

@dhookom,
thanks ,
this field was not intended to have that function. It was first contact info. Latter it was added as description field in subform, and now it makes me a lot of headache, with the data that is already filled in. Date field exists in the tblEvents, but some users didn't fill it because the information was already there in the description field when the account was assigned to the event:
"Why should I enter the date, which is already there! Can't you make it fill-in automatically?"
That is what i'm trying to do. To find all the dates in string field and transfer them to the Date field.

thanks,

 

[Andrzejek]

With the samples of data provided:

Dim str As String
Dim ary() As String
Dim i As Integer
Dim strDate As String
[green]
'str = "press-conference 01.04.2015"
'str = "20.03.2015, annyversary celebration"[/green]
str = "wedding party, 23.05.2015 at Palace Hotel"

ary = Split(str, " ")

For i = LBound(ary) To UBound(ary)
    strDate = Replace(Replace(ary(i), ".", "/"), ",", "")
    If IsDate(strDate) Then
        MsgBox "This is your Date: " & strDate
    End If
Next i

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

As a starting point you may consider a function like this:

Public Function getDigits(myField)
Dim S As Long, l As Long, x As String
If Trim(myField & "") = "" Then
  getDigits = myField
  Exit Function
End If
S = 1
Do While S <= Len(myField)
  x = Mid(myField, S, 1)
  If (x >= "0" And x <= "9") Then
    Exit Do
  End If
  S = S + 1
Loop
l = S
Do While l <= Len(myField)
  x = Mid(myField, l, 1)
  If (x < "0" Or x > "9") And x <> "." Then
    Exit Do
  End If
  l = l + 1
Loop
getDigits = Mid(myField, S, l - S)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BMKanun]

Thanks guys,
it is doing the job!

BMK

 

[Andrzejek]

So what solution did you use? Which one is “doing the job”?
You know, others who will look at his tread may want to know which solution is the one that works.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I agree with Andy. Both he and PH took their time to create solutions for you. It's appropriate to recognize correct replies by clicking the "Great post? Star it!" link.

Duane
Hook'D on Access
MS Access MVP