ExcelBanter - View Single Post

Ron Rosenfeld

On Mon, 10 Dec 2007 06:55:41 -0800 (PST), wrote:

MyVariable = Trim(Mid(data, (InStr(data, ",") - 1)))\

This is the string in wich im trying to Take the date out of,
secondname,thirdname,Mother:1,childname,10/12/2007,

However sometimes it might look like this depending on user input
secondname,thirdname,Mother:1,childname,10/12/2007,10/14/2007

i need Txtbox 1 = the first date, and TxtBox 2 = SecondDate

Here is The Function as is, I asked last week on here but am still
having trouble changing it.

Private Function getfields(data As String, intchoose As Integer) As
String
Dim firstdate As String
Dim seconddate As String
Dim vntX As Variant

vntX = Split(data, ",")

If intchoose = 0 Then
getfields = vntX(0) & "," & vntX(1) ' First Name Last Name
ElseIf intchoose = 2 Then
getfields = vntX(2) ' TxtBox6
ElseIf intchoose = 1 Then
TxtFirstName = vntX(0) ' TxtBox1
TxtLastName = vntX(1) ' TxtBox2
CBOMoFa = vntX(2) ' TxtBox6
TxtChild = vntX(3) ' TxtBox4
End If
End Function
TxtBox 3 and 5 are the dates Txt3 is date 1 Txt5 is date 2

Here is a function that should extract the dates from your string and return it
in an array.

You can check on the number of dates by checking the number of elements in the
array (UBOUND), and pop them into your text fields.

----------------------------------------------
Option Explicit
Function GetDates(str As String)
Dim re As Object, mc As Object, m As Object
Dim temp()
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d{1,2}/\d{1,2}/\d{1,4}"
Set mc = re.Execute(str)
ReDim temp(0 To mc.Count - 1)
For Each m In mc
temp(i) = m
i = i + 1
Next m
GetDates = temp
End Function
-----------------------------------

As written the routine looks for a date "pattern" which I defined as being

one or two digits
followed by a "/"
followed by one or two digits
followed by a "/"
followed by 2 to four digits.

YOu could tighten that up depending on the variability in your date formats.
--ron