Merging a text and date field

[Dave_cha]

I have two fields, one is a text field and the second is a date field which is formatted in the table as yymmdd.
I want to merge the two fields to produce a unique field for another table.
I can do this using [Text field]&[Date field] in a query but the resulting field is [Test dd/mm/yy].
Does anyone know how I can remove the / and reorder the date part of the new field to yymmdd ? Formatting the original date field doesn't seem to work.

Thanks, DC

 

[crosmill]

[Text field] & Mid([Date field], Len([Date field])-8, 2) & Mid([Date field], Len([Date field])-5, 2) & Right([Date field],2)


Try that