Transfer date w/ custom format to a text field?

[DKO]

I'm building a report that requires me to concatenate several fields plus additional words, etc. But not all of the fields are the same data type. I have the date formatted the way I want it in a date/time field in one table (dd mmmm yyyy), and I want to append that date into a text field in another table, maintaining the same format.

Now, when I do a normal append or update query, it appends as medium date format (dd-mmm-yy). If I change the field type in the original table from date/time to text, it also shows up in medium date format.

Any ideas on how to make this work, or other options for concatenating fields with different data types?

edit: I don't want to change the data type of the original field to text.

 

[ByteMyzer]

You might try something along the lines of:

INSERT INTO [b][i]MyDestTable[/i][/b] ( [b][i]MyTextField[/i][/b] )
SELECT Format([b][i]MySourceTable[/i][/b].[b][i]MyDateField[/i][/b], 'dd mmmm yyyy')
FROM [b][i]MySourceTable[/i][/b];

 

[DKO]

Cheers! Guess I need to learn more functions.