I am using an MS Access db to track some tasks during the year. Each task has a due Month. I do not want to use exact dates as the convention in my team is to refer to the month. I don't want to store the dates in a date format as team members will be entering the due month by hand.
Is it possible to sort my fields in date order if the date is stored as a text string month? (eg. January, February rather than 31/01/2009, 28/02/2009).
If so, what would such a query look like?
Thanks in advance.
This should work
SELECT * FROM TableName OrderBy Month(date_field)
If you are storing only the month name, your will first need to convert to a date to get a month number, use a lookup table (MonthNo, MonthName) or use the Switch function. Here is an example of converting to a date:
SELECT Month(CDate(Year(Date()) & "/" & [MonthNameField] & "/1")) AS MonthNo
FROM Table
However, there is probably a good argument for storing a date based on the month name entered, this would prevent any confusion about years.
I would make a date field.
I would store 1/1/2009 for January 2009, 2/1/2009 for February 2009, and so forth. For display purposes, I'd format it so that it displayed only the month (or Month + Year -- can't imagine how you wouldn't want the year).
This makes it possible to take advantage of date operations on the field without messy conversions of text to date formats.
Thank you all for your responses. Sorry for the delay in responding - I'm working on this issue again now.
For clarity, the DB is to be used to track a schedule of events within a 12 month period. The year does not need to be stored as everything in the DB is referring to the same year. A new copy of the DB will be made at the beginning of 2010.
I'm really keen to actually store the month as a word rather than any kind of value or date field as when bulk adding tasks I will likely edit the table directly rather than use a form.