Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
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 now!
  • 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.

st4rcutter (TechnicalUser) (OP)
3 Aug 06 12:10
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!
traingamer (Programmer)
3 Aug 06 12:27

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

SkipVought (Programmer)
3 Aug 06 13:10



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

st4rcutter (TechnicalUser) (OP)
3 Aug 06 16:21
I suppose that will have to work.  I was curious if anyone else could/had come up with a work around.  Thanks anyway Greg!
Golom (Programmer)
3 Aug 06 17:45
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!

Back To Forum

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