Extracting from field

  • Thread starter Thread starter JohnButt
  • Start date Start date
J

JohnButt

I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE DUE

Am I asking for the impossible?
 
I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE DUE

Am I asking for the impossible?
Click to expand...

Very difficult at any rate. Might some records have other date formats, e.g.
2/2/08 or Feb. 2 2008?

You'll probably need to use VBA code using "Regular Expressions" to find the
substring.

IF you can count on the format of the text field being *very* consistant -
that is, only one pair of parentheses anywhere within the field, and always
having a 10-byte long date within the parentheses - you could try

DueDate: DateValue(Mid([ASSESSMENTS_DUE], InStr([ASSESSMENTS_DUE], "(") + 1,
10))


John W. Vinson [MVP]
 
Here is an untested routine that may do what you want.
Copy and paste this function into a VBA module. It returns a string, but
you can change it to return a date or simply change the string into a date.


Public Function fExtractDatePattern(strIn)
Dim i As Long
Dim strDate As String
Const Cs1 As String = "#[-/.]"
Const Cs2 As String = "##[-/.]"
Const Cs2y As String = "##"
Const Cs4y As String = "####"
'Warning this works for US date patterns
'It may or may not work for other patterns

If Len(strIn & "") > 5 Then

For i = 1 To Len(strIn) - 9
If Mid(strIn, i, 10) Like Cs2 & Cs2 & Cs4y And _
IsDate(Mid(strIn, i, 10)) Then 'mm/dd/yyyy
strDate = Mid(strIn, i, 10)
Exit For
End If
Next i

If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 8
If (Mid(strIn, i, 9) Like Cs1 & Cs2 & Cs4y Or _
Mid(strIn, i, 9) Like Cs2 & Cs1 & Cs4y) And _
IsDate(Mid(strIn, i, 9)) Then 'm/dd/yy or mm/d/yyyy
strDate = Mid(strIn, i, 9)
Exit For
End If
Next i
End If

If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 7
If (Mid(strIn, i, 8) Like Cs2 & Cs2 & Cs2y Or _
Mid(strIn, i, 8) Like Cs1 & Cs1 & Cs4y) And _
IsDate(Mid(strIn, i, 8)) Then 'mm/dd/yy or m/d/yyyy
strDate = Mid(strIn, i, 8)
Exit For
End If
Next i
End If

If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 6
If (Mid(strIn, i, 7) Like Cs1 & Cs2 & Cs4y Or _
Mid(strIn, i, 7) Like Cs2 & Cs1 & Cs4y) And _
IsDate(Mid(strIn, i, 7)) Then 'm/dd/yyyy or mm/d/yyyy
strDate = Mid(strIn, i, 7)
Exit For
End If
Next i
End If

If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 5
If Mid(strIn, i, 6) Like Cs1 & Cs1 & Cs2y And _
IsDate(Mid(strIn, i, 6)) Then 'm/d/yy
strDate = Mid(strIn, i, 6)
Exit For
End If
Next i
End If

If Len(strDate) > 0 Then 'found a date
fExtractDatePattern = strDate
'Return a date instead
'If IsDate(strDate) then fExtractDatePattern = CDate(strDate)
End If

End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John W. Vinson said:
I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE
DUE

Am I asking for the impossible?
Click to expand...

Very difficult at any rate. Might some records have other date formats,
e.g.
2/2/08 or Feb. 2 2008?

You'll probably need to use VBA code using "Regular Expressions" to find
the
substring.

IF you can count on the format of the text field being *very* consistant -
that is, only one pair of parentheses anywhere within the field, and
always
having a 10-byte long date within the parentheses - you could try

DueDate: DateValue(Mid([ASSESSMENTS_DUE], InStr([ASSESSMENTS_DUE], "(") +
1,
10))


John W. Vinson [MVP]
Click to expand...
 
I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE DUE

Am I asking for the impossible?
Click to expand...

As long as the text wanted is surrounded with parenthesis and there
are no other parenthesis to the right of it, then try:

=Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

You could run an update query to add the data to a new field:

Update yourTable set YourTable.DateDue =
Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

This would leave the original data intact.
 
I thank you all for responding so quickly - have tried all but with no success.

Fortunately I only normally have about 50 records that I would need to apply
the extraction - so I will rever to the tried and trusted method - cut and
paste.

Thanks very much

fredg said:
I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE DUE

Am I asking for the impossible?
Click to expand...

As long as the text wanted is surrounded with parenthesis and there
are no other parenthesis to the right of it, then try:

=Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

You could run an update query to add the data to a new field:

Update yourTable set YourTable.DateDue =
Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

This would leave the original data intact.
Click to expand...
 
I refer to my previous post - I was entering the suggestion as a criterea but
when I entered the information as a field - perfect result.

You guys are brilliant.

Many Many thanks

fredg said:
I have a table with four fields

One field named ASSESSMENTS_DUE has similar text as follows

COSS OLP/CRP Interim Core Competence Workplace Assessment (02/02/2008)

I wish to extract 02/02/2008 from (02/02/2008) and other variable dates in
other records and possibly append them to a different field called DATE DUE

Am I asking for the impossible?
Click to expand...

As long as the text wanted is surrounded with parenthesis and there
are no other parenthesis to the right of it, then try:

=Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

You could run an update query to add the data to a new field:

Update yourTable set YourTable.DateDue =
Replace(Mid([ASSESSMENTS_DUE],InStrRev([ASSESSMENTS_DUE],"(")+1),")","")

This would leave the original data intact.
Click to expand...
 
Back
Top