Parse dates from text field

[CW]

On our invoices we charge rent for a period and the relevant line (stored in
a table of invoice text lines) reads (as an example) "Rent from 16/04/2010 to
15/05/2010".
I need to pull each of those dates out, to use elsewhere. Can I use some
sort of parsing based on what follows the word "from" and the word "to" - or
any other method! And how, please?
Many thanks
CW

 

[ghetto_banjo]

Assuming that the dates are always following a "from " and a "to ",
one solution is to use the InStr() function. This function searches
for a particular string within a string, and returns the position.

InStr("Rent from 13/04/2010 to 15/05/2010", "from") = 6
InStr("Rent from 13/04/2010 to 15/05/2010", "to ") = 22

Now the slight problem with this, is that "to" is a common character
string that could appear in other words. You can minimize the the
problem by searching for "to " or even better " to ". Just remember
that if you search for " to " the function is returning the position
of that first space.

Once you know where the words "from" and "to" are location, you can
use the Mid function to return your dates, using CDate to convert to
an actual date value.

CDate(Mid("Rent from 13/04/2010 to 15/05/2010", 11, 10)) = 13/04/2010

The reason we use 11 is because we know the date starts exactly 5
characters past the start of "from", which InStr showed = 6. The 10
means to grab 10 characters starting at that position.

 

[Hans Up]

On our invoices we charge rent for a period and the relevant line (stored in
a table of invoice text lines) reads (as an example) "Rent from 16/04/2010 to
15/05/2010".
I need to pull each of those dates out, to use elsewhere. Can I use some
sort of parsing based on what follows the word "from" and the word "to" - or
any other method! And how, please?

If it is always in that format, consider the Split() function. Your two
date strings would be the third and fifth elements of the array returned
by Split:

Dim fdate as String
Dim tdate as String
fdate = Split("Rent from 16/04/2010 to 15/05/2010")(2)
tdate = Split("Rent from 16/04/2010 to 15/05/2010")(4)

Substitute your field name for the literal text string.