Date Extraction

[marathonlady]

using mid function to extract date

How do I extract a date from a string using the mid function when I don't know the length of the date field, ie, it could be 2/1/03 (6 long) or 12/23/03 (8 long)? The date will be starting in the same spot in the string I just want to replace it with the next date in the table.

Thanks.

 

[FoFa]

What follows the 03 (year) in the string? Since you know the starting position, an instr could look for the character following the year and then you the starting (known) and ending+1 column. So if it was followed by a space say this woulod get it (assume start in column 5)

StartCol = 5
StringVar = mid(String1,StartCol,instr(StartCol,String1," ")-StartCol)

As an example, you might have to play with it a little.

 

[Nouba]

You could use the DateValue function on the string, add one day and then use the Format function on that date. For me it make more sense to store a date into a date/time field.

 

[raskew]

Since you say that the start point will always be the same and provided that the string representation doesn't contain any spaces (e.g. 21 Dec 03), you could try this from the debug window:

'j = start point (first position of date)
j = 24

x = "I made my last payment 2/1/03 or thereabouts."
y = mid(x, 24, instr(j, x, " ")-j)
? y
2/1/03

x = "I made my last payment 12/21/03 or thereabouts."
y = mid(x, j, instr(j, x, " ")-j)
? y
12/21/03

x = "I made my last payment 29-Dec-2003 or thereabouts."
y = mid(x, j, instr(j, x, " ")-j)
? y
29-Dec-2003

Having said that, you really need to give thought to dumping string/date from your applications and using true date/time dates. Representing a date as a string negates your immediate use of all of Access' built-in date/time functions and adds an unneeded layer of complexity.