text field to date field field

A

andyb

I have a database that has a text field called "visit" it has been
filled with data ie., " 6/22/07 - not good, " the field may also
be blank or it may contain random text after the date. In some cases
no date at all just text. I would like to create a new field called
DateVisit that contains just the dates that were entered in the field
"visit"

it has been a struggle and I have made no progress

please help

(e-mail address removed)/nospam
 
J

Jeff Boyce

From your description, the field might contain a date, or a date and text,
or text, or nothing.

How would you explain to a live person how to tell the difference? How do
you propose to tell Access how to tell the difference?

How many of these do you have?

I suppose one alternative would be (depending on which version of Access you
are using) to use the Split() function to see if any of the pieces can be
converted to an Access Date/Time value. You'll probably need to create your
own function to "split" each record's value in that field, determine if any
of the split pieces are date/times, then save those found to a new field.

Got VB?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

andyb

From your description, the field might contain a date, or a date and text,
or text, or nothing.

How would you explain to a live person how to tell the difference?  How do
you propose to tell Access how to tell the difference?

How many of these do you have?

I suppose one alternative would be (depending on which version of Access you
are using) to use the Split() function to see if any of the pieces can be
converted to an Access Date/Time value.  You'll probably need to create your
own function to "split" each record's value in that field, determine if any
of the split pieces are date/times, then save those found to a new field.

Got VB?

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Use the split function in a query?

I have 747 records like these.

I would tell a person to ignore any alpha characters, and emty fields

Andy
 
J

John W. Vinson

I have a database that has a text field called "visit" it has been
filled with data ie., " 6/22/07 - not good, " the field may also
be blank or it may contain random text after the date. In some cases
no date at all just text. I would like to create a new field called
DateVisit that contains just the dates that were entered in the field
"visit"

it has been a struggle and I have made no progress

please help

(e-mail address removed)/nospam

If you can assume that the leftmost portion of the string (with any blanks
trimmed off) is a date, or else that there is no date, then try:

IIf(IsDate(Left(Trim([visit], InStr([visit], " ") - 1)),
CDate((Left(Trim([visit], InStr([visit], " ") - 1)), Null)


John W. Vinson [MVP]
 
J

John Spencer

First question is there MORE than one date in the field? Assuming only one
date the function below should work to give you the date as a string.

Function fExtractDate(strIn)
Dim s As Variant
Dim i As Long

If Len(strIn & "") = 0 Then Exit Function

s = Split(strIn, " ")
For i = LBound(s) To UBound(s)
If IsDate(s(i)) Then
fExtractDate = s(i)
' Or if you wanted the date back as a datetime field
' fExtractDate = CDate(s(i) )
Exit Function
End If
Next i

End Function

In your query you would call it as
Field: TheDate: fExtractDate([Name of the field])
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

From your description, the field might contain a date, or a date and text,
or text, or nothing.

How would you explain to a live person how to tell the difference? How do
you propose to tell Access how to tell the difference?

How many of these do you have?

I suppose one alternative would be (depending on which version of Access
you
are using) to use the Split() function to see if any of the pieces can be
converted to an Access Date/Time value. You'll probably need to create
your
own function to "split" each record's value in that field, determine if
any
of the split pieces are date/times, then save those found to a new field.

Got VB?

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Use the split function in a query?

I have 747 records like these.

I would tell a person to ignore any alpha characters, and emty fields

Andy
 
A

andyb

I have a database that has a text field called "visit"  it has been
filled with data ie.,    " 6/22/07 - not good, "   the field may also
be blank or it may contain random text after the date. In some cases
no date at all just text. I would like to create a new field called
DateVisit that contains just the dates that were entered in the field
"visit"
     it has been a struggle and I have made no progress
please help
(e-mail address removed)/nospam

If you can assume that the leftmost portion of the string (with any blanks
trimmed off) is a date, or else that there is no date, then try:

IIf(IsDate(Left(Trim([visit], InStr([visit], " ") - 1)),
CDate((Left(Trim([visit], InStr([visit], " ") - 1)), Null)

             John W. Vinson [MVP]


Hi John

It did not work. I kept getting wrong number of arguments error.
 
J

John Spencer

This might work if the Date is always right at the beginning of the field.


IIF(IsDate(Left(Trim(Visit & ""),Instr(1,Trim(Visit & ""),
" ")),Left(Trim(Visit),Instr(1,Trim(Visit)," "),Null)

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

I have a database that has a text field called "visit" it has been
filled with data ie., " 6/22/07 - not good, " the field may also
be blank or it may contain random text after the date. In some cases
no date at all just text. I would like to create a new field called
DateVisit that contains just the dates that were entered in the field
"visit"
it has been a struggle and I have made no progress
please help
(e-mail address removed)/nospam
If you can assume that the leftmost portion of the string (with any blanks
trimmed off) is a date, or else that there is no date, then try:

IIf(IsDate(Left(Trim([visit], InStr([visit], " ") - 1)),
CDate((Left(Trim([visit], InStr([visit], " ") - 1)), Null)

John W. Vinson [MVP]


Hi John

It did not work. I kept getting wrong number of arguments error.
 
A

andyb

This might work if the Date is always right at the beginning of the field.

IIF(IsDate(Left(Trim(Visit & ""),Instr(1,Trim(Visit & ""),
" ")),Left(Trim(Visit),Instr(1,Trim(Visit)," "),Null)

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


I have a database that has a text field called "visit"  it has been
filled with data ie.,    " 6/22/07 - not good, "   the field mayalso
be blank or it may contain random text after the date. In some cases
no date at all just text. I would like to create a new field called
DateVisit that contains just the dates that were entered in the field
"visit"
     it has been a struggle and I have made no progress
please help
(e-mail address removed)/nospam
If you can assume that the leftmost portion of the string (with any blanks
trimmed off) is a date, or else that there is no date, then try:
IIf(IsDate(Left(Trim([visit], InStr([visit], " ") - 1)),
CDate((Left(Trim([visit], InStr([visit], " ") - 1)), Null)
             John W. Vinson [MVP]
      It did not work. I kept getting wrong number of arguments error.- Hide quoted text -

- Show quoted text -

I have been putting the statement in the criteria in design mode and
it is just not working
 
A

andyb

First question is there MORE than one date in the field?  Assuming only one
date the function below should work to give you the date as a string.

Function fExtractDate(strIn)
Dim s As Variant
Dim i As Long

   If Len(strIn & "") = 0 Then Exit Function

   s = Split(strIn, " ")
   For i = LBound(s) To UBound(s)
       If IsDate(s(i)) Then
         fExtractDate = s(i)
         ' Or if you wanted the date back as a datetime field
         ' fExtractDate = CDate(s(i) )
          Exit Function
      End If
   Next i

End Function

In your query you would call it as
Field: TheDate: fExtractDate([Name of the field])
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


From your description, the field might contain a date, or a date and text,
or text, or nothing.
How would you explain to a live person how to tell the difference? How do
you propose to tell Access how to tell the difference?
How many of these do you have?
I suppose one alternative would be (depending on which version of Access
you
are using) to use the Split() function to see if any of the pieces can be
converted to an Access Date/Time value. You'll probably need to create
your
own function to "split" each record's value in that field, determine if
any
of the split pieces are date/times, then save those found to a new field..
Got VB?

Jeff Boyce
Microsoft Office/Access MVP
- Show quoted text -

Use the split function in a query?

I have 747 records like these.

I would tell a person to ignore any alpha characters, and emty fields

Andy- Hide quoted text -

- Show quoted text -

It works nice until it encounters a character right next to the date.
ie., 09/22/06-good
the - or letter next to the last number messes it up
 
J

John W. Vinson

I have been putting the statement in the criteria in design mode and
it is just not working

Sorry... should have explained better. This is not a CRITERION on a field;
this is instead a calculated date/time field extracted from the text field.
Put the expression in a vacant Field cell in the query grid.

John W. Vinson [MVP]
 
J

John Spencer

Now you know the reason that having this data stored with other data items
is a bad idea.

Using Regular expression to snag the date looks like your best choice. I'm
not up to that challenge.

You would need to find and extract data that matched the pattern
1 or 2 numbers, a separator character, 1 or 2 numbers, a separator
character, and 2 or 4 numbers.

You can try the following function (not fully tested). It looks for date
strings in several configurations in order of length.
-- It first looks for a 10 character date mm/dd/yyyy and returns the first
instance of that
-- If no 10 character date is found it looks for a 9 character date
m/dd/yyyy or mm/d/yyyy and returns the first instance of that
-- (8) Then m/d/yyyy or mm/dd/yy
-- (7) Then m/d/yy or mm/d/yy or m/dd/yy
-- (6) Then m/d/yy

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, 7) 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
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
..

It works nice until it encounters a character right next to the date.
ie., 09/22/06-good
the - or letter next to the last number messes it up

First question is there MORE than one date in the field? Assuming only one
date the function below should work to give you the date as a string.

Function fExtractDate(strIn)
Dim s As Variant
Dim i As Long

If Len(strIn & "") = 0 Then Exit Function

s = Split(strIn, " ")
For i = LBound(s) To UBound(s)
If IsDate(s(i)) Then
fExtractDate = s(i)
' Or if you wanted the date back as a datetime field
' fExtractDate = CDate(s(i) )
Exit Function
End If
Next i

End Function

In your query you would call it as
Field: TheDate: fExtractDate([Name of the field])
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
 
A

andyb

Now you know the reason that having this data stored with other data items
is a bad idea.

Using Regular expression to snag the date looks like your best choice.  I'm
not up to that challenge.

You would need to find and extract data that matched the pattern
1 or 2 numbers, a separator character, 1 or 2 numbers, a separator
character, and 2 or 4 numbers.

You can try the following function (not fully tested).  It looks for date
strings in several configurations in order of length.
-- It first looks for a 10 character date mm/dd/yyyy and returns the first
instance of that
-- If no 10 character date is found it looks for a 9 character date
m/dd/yyyy or mm/d/yyyy and returns the first instance of that
-- (8) Then m/d/yyyy or mm/dd/yy
-- (7) Then m/d/yy or mm/d/yy or m/dd/yy
-- (6) Then m/d/yy

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 ormm/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, 7) 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
      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
.

It works nice until it encounters a character right next to the date.
ie., 09/22/06-good
the - or letter next to the last number messes it up


First question is there MORE than one date in the field? Assuming only one
date the function below should work to give you the date as a string.
Function fExtractDate(strIn)
Dim s As Variant
Dim i As Long
If Len(strIn & "") = 0 Then Exit Function
s = Split(strIn, " ")
For i = LBound(s) To UBound(s)
If IsDate(s(i)) Then
fExtractDate = s(i)
' Or if you wanted the date back as a datetime field
' fExtractDate = CDate(s(i) )
Exit Function
End If
Next i
End Function
In your query you would call it as
Field: TheDate: fExtractDate([Name of the field])
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.- Hide quoted text -

- Show quoted text -

I am sorry I have not got back to anyone as of yet. What I ended up
doing was running the function extract by John Spencer. What it
missed as a result of random characters being to the right I
eliminated with a simple search and replace. I then ran the function
again and it was all fixed.

Thanks to all for the great stuff suggested. Sorry again for the
late reply

Andy Benjamin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top