Extract Date

[RuralGuy]

As you could see in my code, if there was no date at the end of the Details field the code returns the date 1/1/1900. Is that acceptable?

[RuralGuy]

As you could see in my code, if there was no date at the end of the Details field the code returns the date 1/1/1900. Is that acceptable?

[bigroo]

Thanks RuralGuy for pointing that out, I am in the process of learning VBA.

[bigroo]

Well this will not work in some cases, I most likely have updated that record to the correct date of that entry. So, if there is not date leave that records date as is and move on.

[RuralGuy]

We can set it up to use the original field date if there is no date at the end of the Details field. Is that acceptable?

[bigroo]

That would be acceptable!

[RuralGuy]

Hmm...something fishy with the data. It may take a bit.

[RuralGuy]

The date at the end of the Memo field is preceeded by Chr(160) and not a space. Will that always be consistant?

[bigroo]

For the memo fields with the date on the end, I would say they would be consistant, they all were copied from the same website, so yes!
The other rows without the dates were either copy and paste or hand typed.

[bigroo]

I am curious how you discovered this fact?

[RuralGuy]

I am curious how you discovered this fact?

My code was not finding the first *SPACE* before the date. It instead was going further back into the memo field. Then it was simply a matter or decoding those bytes.

[orange]

I don't think it is a space before the date in your details field. I'm finding chr 160.

I ran this query

SELECT 
  tblFacHistory.[Date Started]
, tblFacHistory.Details
, InStrRev([details],Chr(160)) AS x
FROM tblFacHistory
WHERE
 (((tblFacHistory.Details) Like "*" & Chr(160) & "*"));

and this is a portion of what I received.

Date Started
Details
x

4/4/2012
New Rock 90.7 from The Alternative 6/26/2003
35

4/4/2012
Religious from cp-new 7/26/2011
22

4/4/2012
cp-new from  7/23/2008
13

4/4/2012
Radio 74 Internationale from  7/26/2011
30

4/4/2012
KGHWFM from 177344FM 9/17/2010
21

4/4/2012
90.7 from 90.9 3/11/2009
15

4/4/2012
Black Hills State University from Educational Media Foundation, Inc. 6/2/2009
69

4/4/2012
Educational Media Foundation, Inc. from American Family Association 3/27/2007
68

4/4/2012
Adult Altern. from cp-new 4/13/2009
26

4/4/2012
The Buzz from  4/13/2009
15

[orange]

The update query would be

UPDATE tblFacHistory 
SET [Date Started] = Mid([details],InStrRev([details],Chr(160)))
WHERE (((Details) Like "*" & Chr(160) & "*"));

7383 records would be updated.

[bigroo]

Hi Orange,

I am curious what does the numbers under column X indicate?

[orange]

Hi Orange,

I am curious what does the numbers under column X indicate?

That is the position in the field Details where the Chr(160) occurs.

In fact it is the character before the start of the Date in those Detail records that have the Date.
So the Date we are looking for (to do the update) is between that x position and the end of the Details field.

I have revised the select query to show what would be used to update the Date Started. I called it ToReplace

Here's the query

SELECT [Date Started]
, Details, InStrRev([details],Chr(160)) AS x
, Mid([details],InStrRev([details],Chr(160))) AS ToReplace
FROM tblFacHistory
WHERE (((tblFacHistory.Details) Like "*" & Chr(160) & "*"));

and here's some sample output

Date Started	Details	x	ToReplace
4/4/2012	New Rock 90.7 from The Alternative 6/26/2003	35	6/26/2003
4/4/2012	Religious from cp-new 7/26/2011	22	7/26/2011
4/4/2012	cp-new from 7/23/2008	13	7/23/2008
4/4/2012	Radio 74 Internationale from 7/26/2011	30	7/26/2011
4/4/2012	KGHWFM from 177344FM 9/17/2010	21	9/17/2010
4/4/2012	90.7 from 90.9 3/11/2009	15	3/11/2009
4/4/2012	Black Hills State University from Educational Media Foundation, Inc. 6/2/2009	69	6/2/2009
4/4/2012	Educational Media Foundation, Inc. from American Family Association 3/27/2007	68	3/27/2007
4/4/2012	Adult Altern. from cp-new 4/13/2009	26	4/13/2009
4/4/2012	The Buzz from 4/13/2009	15	4/13/2009
4/4/2012	KJKTFM from KOARFM 3/26/2009	19	3/26/2009
4/4/2012	KOARFM from KSPFFM 5/4/2007	19	5/4/2007
4/4/2012	KSPFFM from 92516FM 4/26/2006	20	4/26/2006
4/4/2012	Calvary Chapel of Twin Falls, Inc. from CSN International 11/5/2007	58	11/5/2007
4/4/2012	Religious from cp-new 3/10/2008	22	3/10/2008
4/4/2012	CSN International from The Cause 12/1/2008	33	12/1/2008
4/4/2012	The Cause from 3/10/2008	16	3/10/2008
4/4/2012	KWRCFM from 90500FM 3/16/2005	20	3/16/2005