Convert from DATE to TEXT and back again.

[st4rcutter]

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]

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]

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,
[sub]
[red][/red]
[/sub]

 

[st4rcutter]

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]

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

Select Format([SomeDate], "MMMM")

From myTable

Order By Month([SomeDate])

Will Produce the month names and the chronological order you are looking for.