Copy Field Minus Some Characters

[rfear]

I am linking to another database and want to convert this field JJ3-1-2009 into a date field 03/01/09 by losing the first 2 characters.

Any ideas on how to do this ?

 

[pbaldy]

Right(FieldName, Len(FieldName) - 2)

 

[rfear]

(Thanks. I'll give this a go when I get into work tomorrow.)
09/01/09 - Almost working. The expression does as expected but I suspect the answer it gives is not in date format as when I go to sort the field in ascending order, it doesn't. I'll have a play around and see what I can do.

 

[rfear]

Week Ending: Format((Right([Ident],Len([Ident])-3)),"dd/mm/yy")

OK, the obvious attempt has not worked.

Ident : Week Ending
JJ1-12-2007 : 01/12/07
JJ2-9-2006 : 02/09/06
JJ1-11-2006 : 04/11/06

When I sort in ascending order it is putting all the 1st days together, then the 2nd, then the 3rd, then the 4th etc.

It does not recognise the field as being a date ?

I can't right click and set properties to date format, tried using '#' characters but to no avail ?

 

[rfear]

Week Ending: DateValue(Format((Right([Ident],Len([Ident])-3)),"dd/mm/yyyy"))

Sorted

 

[neileg]

Week Ending: CDate(Right([Ident],Len([Ident])-3))

Neater