Dates in one field

[Guest]

I have a field that contains dates in the following format:
1/1/03 - 1/2/03
11/1/03 - 11/2/03

I would like to be able to split the dates but I am unable
to use the "left" function. The space before the dash and
after the dash is consistent but I don't know how to use a
function that will cut off the date based on the space in
the middle. In excel it is easy to do by using the
delimited function.

Does anyone know what I could use for this?

I would really appreciate your help.

 

[Brian Camire]

You might use (say, in a query) an expression like this

CDate(Trim(Left(YourField, InStr(1, YourField, "-") - 1)))

to get the "start" date, and one like this

CDate(Trim(Mid(YourField, InStr(1, YourField, "-") + 1)))

to get the "end" date.

 

[Guest]

It works perfect

Thank you very much.

-----Original Message-----
You might use (say, in a query) an expression like this

CDate(Trim(Left(YourField, InStr(1, YourField, "-") - 1)))

to get the "start" date, and one like this

CDate(Trim(Mid(YourField, InStr(1, YourField, "-") + 1)))

to get the "end" date.




.