Extract Date

[bigroo]

Hello and Happy Thanksgiving to our US Readers.



Here is my issue.

Date Started	Details
01/01/1900	Silent from Black Gospel 5/19/2003
01/01/1900	Oldies from Adult Stand. 11/1/2007
01/01/1900	Format
01/01/1900	Amistad Communications from Greenwood Acres Baptist Church 3/8/2006
01/01/1900	Robert J. and Mary J.E. Whitaker, LLC from Amistad Communications 7/9/2010
01/01/1900	Owner

What I would like to do is cleanup this table using a query. Is there a way to extract the date where there is one from the end of the details field and insert the Date extracted nto the Date started field, I have approx 100,000 rows.

Any guidance or help would be appreciated.

Thanks

[RuralGuy]

It looks to me like the [Date Started] field is a String field as well. Is that true?

[orange]

This query may get you started. This will get the valid dates from the details field.

SELECT Bigroo.Details,
Mid([dETAILS],InStrRev([Details]," ")+1,Len([Details])-InStrRev([Details]," ")) AS x,
 FROM Bigroo
WHERE (((IsDate(Mid([dETAILS],InStrRev([Details]," ")+1,Len([Details])-InStrRev([Details]," "))))=True));

Result based on your data

Details	x
Silent from Black Gospel 5/19/2003	5/19/2003
Oldies from Adult Stand. 11/1/2007	11/1/2007
Amistad Communications from Greenwood Acres Baptist Church 3/8/2006	3/8/2006
Robert J. and Mary J.E. Whitaker, LLC from Amistad Communications 7/9/2010	7/9/2010

[bigroo]

Hi RuralGuy,
No, [Date Started] is a Date field

Thanks

[bigroo]

Thanks Orange,

Here is what I have, but I get an error message when I try to run.

SELECT tblFacHistory.Details,
Mid([Details],InStrRev([Details]," ")+1,Len([Details])-InStrRev([Details]," ")) AS x,
FROM tblFacHistory,
WHERE (((IsDate(Mid([Details],InStrRev([Details]," ")+1,Len([Details])-InStrRev([Details]," "))))=True));

"The Select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect"

I looked at the SQL statement and looks find.

Thanks

[RuralGuy]

Here's a Public Function for a Standard Module that could then be used like:

Set [Date Started] = GetDate([Details])

Public Function GetDate(InVal As String) As Date
' Return the date on the end of the Incoming String
' ie: "Oldies from Adult Stand. 11/1/2007"
   Dim InDate As String
   InDate = Mid(InVal, InStrRev(InVal, " "))
   If IsDate(InDate) Then
      GetDate = DateValue(InDate)
   Else
      GetDate = DateValue("1/1/1900")
   End If
End Function

[bigroo]

Thanks RuralGuy,

I seem to have #Name? in the Date Started field now. I checked the spelling of names and all looks good.
I have this as the form Control Source Set[Date Started]=GetDate([Details]) entered in the date started field. This is where it is to godd correct?

[RuralGuy]

You would need to use it in an Update query.

[orange]

You have an extra comma after the tablename and after the x. These should both be removed.

[bigroo]

Hi Rural Guy, thanks for your patiance.
I am still having issues getting this to work. I created an update query with Date Started and Details in the grid from tblfachistory table. Under the Date Started update to row I enter Set[Date Started]=GetDate([Details]) but it will not accept this it changes it expression to this "Set[Date Started]"=GetDate([Details])
I have error message as "Undefined Function 'GetDate' in Expression

[bigroo]

Hi Orange, thanks for your patiance.

Still no luck in getting the SQL statement to run either, still receiving the same error

[bigroo]

Hello all,

I am in the process of uploading a sample of the database, just stripping it down from 114mb.

[RuralGuy]

The easiest way to strip down a db is to *import* it into a new fresh db and only select what you need.

[bigroo]

Here is the database stripped down.
Maybe you can see what I am doing wrong!

[RuralGuy]

To start, you cannot name the Standard Module the same as the procedure it contains; or any other procedure for that matter.