INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Convert from DATE to TEXT and back again.

Convert from DATE to TEXT and back again.

(OP)
I'm familiar with the FORMAT() and CDate() functions, but I'm having a little difficulty getting this to work on what would seem a very simple problem. Here is what I am trying to accomplish:

I have a valid date format...example 7/03/2006.  I determine the month with Format([qryInstalls]![Date1],"mmmm").  Once I have the month value on each record I would like to be able to sort the records chronologically by month name.  Since the month format is now text, the sort is alphabetical...April, August, February...etc.  I need to be able to determine the month name from the date while still maintaining a date format.  I tried using Monthname(month([qryInstalls]![Date1],"mmmm")) but Monthname is not supported when you export this data to Excel which is something I must be able to do.  I also can't sort on a separate column with monthnumber because I need these month names to be valid dates for sorting in Excel.  Any ideas?  CDate() does not accomplish the task either.  Thank you!

RE: Convert from DATE to TEXT and back again.

Quote:

Once I have the month value on each record I would like to be able to sort the records chronologically by month name.
As you have pointed out, this can't be done.

One relatively simple solution is to export the full date to Excel, then format the date column in excel to 'custom' 'mmmm' - the dates are stored as dates, displayed as names and sortable.
Am I overlooking something?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

RE: Convert from DATE to TEXT and back again.




Hi,

Ditto to Greg.

I strongly advise against changing your dates to ANY kind of STRING!

Fealize that the FORMAT function returns a STRING, and sans DAY and YEAR, it would be impossible to reconstruct the original date value.

Skip,

glasses
tongue

RE: Convert from DATE to TEXT and back again.

(OP)
I suppose that will have to work.  I was curious if anyone else could/had come up with a work around.  Thanks anyway Greg!

RE: Convert from DATE to TEXT and back again.

What you display and how you sort need not be the same things. For example

CODE

Select Format([SomeDate], "MMMM")

From myTable

Order By Month([SomeDate])
Will Produce the month names and the chronological order you are looking for.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close