Get Date from string

[Marange]

Importing a xls file into Access - using windows API browser to select the xls file on the server - once selected the full path and filename is stored in a tbxPath.
Example: Z:\\051Share\Vacancy\Web Vacancy List as of 11-12-12
I want to capture the date from this string and append to the imported table.
Looking for code that will parse the string and return 11-12-12 as a date that could be appended to records in the table.

 

[Isskint]

Isolate the "date" part using Mid() or Right() etc then use DateValue() function. EG if the date will always be in the format you show in the example then;

Variable or Field = DateValue(Right(FileName,8))

 

[Marange]

Isolate the "date" part using Mid() or Right() etc then use DateValue() function. EG if the date will always be in the format you show in the example then;

Variable or Field = DateValue(Right(FileName,8))

Isskint - thanks - It worked - but I discovered issues with the string actually it is the full path to files selected via browser- and may end in .xls or .xlsx - so now i'm looking for a way to capture the string preceeding the dot (.xls) or better yet, capturing the part that begins and ends with numerals - as the date could vary Ex: 1-5-12.xls or 04-05-12.xlsx or 03-4-2012.xls

Does this bring a function to mind? :banghead::banghead:

 

[Isskint]

You would just need to use the Instr() and Left() functions. Use Instr() to find the period eg .xls. With that located use the Left() function to reduce FileName.

So code would look something like:

Dim xVar as Integer
Dim xFileName as String

xVar = Instr(FileName,".")
xFileName = Left(FileName,(xVar-1))
Variable or Field = DateValue(Right(xFileName,8))

Or you could use an all in one formula (not as easy to debug if there are any issues however)

Variable or Field = DateValue(Right(Left(FileName,((Instr(FileName,"."))-1)),8))

 

[Marange]

Wow
Thanks - that did the trick and is much appreciated.