extract date from text field

S

Sparky

Hello,
I am new to access (2003) so may not use the correct terminology.

I want to extract a 'date' from a text field to populate another field. As
well I need to extract a string of numbers to fill another field.

SceneID=catDALI_a2155342430807171842022J_a26S5G2J200807171841140LMLMMX_satShift0_seg5278220.

The KJ to extract is 534243
The SceneDate to extract is 080717

The SceneID will always have the same structure.
 
J

Jerry Whittle

If you just want 080717:

SceneDate: Mid([SceneID],19,6)

If you want an actual date the following should work assuming that SceneDate
080717 is 7/17/2008:

SceneDate: CDate(Mid([SceneID],21,2) & "/" & Mid([SceneID],23,2) & "/" &
Mid([SceneID],19,2))

I'm also assuming that the string always has the same number of characters
in the same order. Plus the date part of the string will always be a valid
date. CDate chokes on an invalid date.

KJ: Mid([SceneID],13,6)
 

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

Similar Threads

Excel Extract Text from decimal separated string 6
Extracting part of a field 2
Selecting characgers from a text field 3
Extracting Date from Date/Time Field 2
Extract numbers 9
Extract specific information from the body of outlook mail to an Excel File using VBA 0
extracting numbers from a string 6
printing report with date 3

Top