Extract date range from String

[dev1212]

Hi,
I have a field 'Note' which is string and it has records like

'Market for to 02/02/2010 03/21/2010'
'Statistics in 01/04/2010 to 05/21/2010'
'Rules imposed from 01/01/2010 to 01/21/2010'
'Assessment for 02/05/2010 to 03/25/2010'
'Look up 01/07/2010 to 03/21/2010'
.....

i want to get the date range in this field like
'02/02/2010 03/21/2010'
'01/04/2010 to 05/21/2010'
'01/01/2010 to 01/21/2010'
...

i tried with 'Left' function like..
Left({Table.Note},Instr({?SelectionCriteria},'/')-2, ????)
I dont know what to write in it..

Any other approach would be greatly appreciated..

Thanks

 

[dev1212]

sorry i misquoted it..
its
Left({Table.Note},Instr({Table.Note},'/')-2, ????)

 

[kray4660]

I would think you would want to use the right function instead. But I see that it is inconsistant on the format of the dates.

I can think of a way, but I do not know if it is the best.
Right({Table.Note}, Len({Table.Note})-Instr({Table.Note,'/')-2)). My math may be off a couple of characters, but the idea is to just grab the rightmost part of the string.

 

[dev1212]

Never mind.. got it figured out
As the length of date in 'note' is constant..

{Table.Note} [Instr({Table.Note},'/')-2 to Instr({Table.Note},'/')+21]

 

[fisheromacse]

if the length of the value you are trying to extract is constant, how about something like this:

Right(TRIM({Table.Note}),24)

 

[IanWaterman]

If date is always at end of note and field is a VARCHAR

mid({Table.Note}, Instr({Table.Note},'/')-2)

Ian