By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Convert a date to a text string

P: 1
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!
Feb 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: 49
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.
Feb 19 '07 #2

ADezii
Expert 5K+
P: 8,638
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::
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.TestDate = Format([TestDate],"dd-mmm-yyyy")
  2. WHERE tblTest.TestDate Is Not Null;
Feb 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.