Convert a date to a text string

[Kakishev]

I have a problem at how best to convert a Date into a text field and keep the format dd/mmm/yyyy (01-FEB-2007).

The problem is that dates are imported from SQL into an access front end Database. When the dates are imported they are in the format dd/mm/yyyy.
However on the reports the dates are in the in the format dd/mmm/yyyy.

I now need to allow the fields to accept an asterix (*) as a prefix to the date to show dates that have not been imported but input manually by users.

To allow for the asterix I have changed the date/time field to a text field in the access front end. However, this now displays the date as dd/mm/yyyy and i need to format this date to dd/mmm/yyyy to give 01-Feb-2007.
Ideas greatly appriciated!

[tehgreatmg]

The only thing I can think of to do is to break the date up to seperate dd mm yyyy. Then make a statement to compare the mm and set it to your mmm format and then just recombine everything for an output. This is probably the longest way around, I have only been using vb for about 1 yr now so I dont know many shortcuts, hope this helps.

[ADezii]

I have a problem at how best to convert a Date into a text field and keep the format dd/mmm/yyyy (01-FEB-2007).

The problem is that dates are imported from SQL into an access front end Database. When the dates are imported they are in the format dd/mm/yyyy.
However on the reports the dates are in the in the format dd/mmm/yyyy.

I now need to allow the fields to accept an asterix (*) as a prefix to the date to show dates that have not been imported but input manually by users.

To allow for the asterix I have changed the date/time field to a text field in the access front end. However, this now displays the date as dd/mm/yyyy and i need to format this date to dd/mmm/yyyy to give 01-Feb-2007.
Ideas greatly appriciated!

This should do the trick assuming your Date Field is named [TestDate] in a Table named tblTest::

UPDATE tblTest SET tblTest.TestDate = Format([TestDate],"dd-mmm-yyyy")
WHERE tblTest.TestDate Is Not Null;