Moving Date Information

[sjd]

I am trying to split a field that typically contains a revision level and a revision date in a single entry as follows: (Rev Level) {Space} (Date: MM/DD/YY)
REL 10-30-14
A 11-20-14
C 11/13/14

Note: dates use either - or / as separators. 99% of the time, there is a space between revision level and date.

Is there a simple way to write an update query to take the value in the current field, strip away all letters and spaces (but not - or /), and insert the remaining value into the revision_date field?

 

[CJ_London]

to split your string use string functions.

myDatefield = mid(revisionfield,instr(revisionfield," ")+1)
 
myRevfield=left(revisionfield,instr(revisionfield," ")-1)

 

[namliam]

Is the date 99% of the time in ACTUAL MM/DD/YY ???
I.e. NOT
9-30-14
10/1/14
10/1/2014

but always 2 month numbers, 2 day number, 2 years ????
If so... Simply do Right(Yourfield,8)

otherwize, you will probably be beter of creating a UDF

 

[JHB]

If the date always is in the end and 6 number long, then use the right function.
Here is a link which shows some example:
http://office.microsoft.com/en-us/access-help/using-string-manipulation-functions-HA001054727.aspx

 

[sjd]

I ended up using the mid function since the date was not always MM/DD/YY -- EX: 1/24/14 was not 01/24/14. There were also some garbled entries, no spaces or malformed dates 01/08/08/14 type entries.

I ended up with something like this:

UPDATE items
SET revision_date = 
IIF( ISDATE(mid(revision_level,instr(revision_level," ")+1), mid(revision_level,instr(revision_level," ")+1), null) 
WHERE
revision_level like "*#*" and revision_date is null

Just to make sure the revision level actually had a number in it. Verify the number is actually a date entry. And not to overwrite any that had the date manually entered already.

Hope it helps anyone else that runs into this at some point.