在MS Access中按月排序

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.

我使用MS Access数据库来跟踪一年中的一些任务。每个任务都有一个到期月份。我不想使用确切的日期,因为我的团队的惯例是引用月份。我不希望以日期格式存储日期,因为团队成员将手动进入截止月份。

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).

如果日期存储为文本字符串月份,是否可以按日期顺序对字段进行排序? (例如,1月,2月而非2009年1月31日,2009年2月28日)。

If so, what would such a query look like?

如果是这样,这样的查询会是什么样子?

Thanks in advance.

提前致谢。

6 个解决方案

#1

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:

如果您只存储月份名称,则首先需要转换为日期以获取月份编号,使用查找表(MonthNo,MonthName)或使用切换功能。以下是转换为日期的示例:

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.

但是,根据输入的月份名称存储日期可能是一个很好的理由,这可以防止任何关于年份的混淆。

#2

This should work

这应该工作

SELECT * FROM TableName OrderBy Month(date_field)

#3

I would store the month as an integer 1-12 then you can easily sort them.

我会将月份存储为1-12的整数,然后您可以轻松地对它们进行排序。

#4

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).

我将在2009年1月1日,2009年2月2日的2009年2月1日存储,等等。出于显示目的,我将其格式化,使其仅显示月份(或月份+年份 - 无法想象您不希望年份如何)。

This makes it possible to take advantage of date operations on the field without messy conversions of text to date formats.

这样就可以利用字段上的日期操作,而不会出现文本到日期格式的混乱转换。

#5

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.

为清楚起见,数据库将用于跟踪12个月内的事件计划。年份不需要存储,因为数据库中的所有内容都指的是同一年。数据库的新副本将于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.

我真的很想将月份存储为单词而不是任何类型的值或日期字段,因为批量添加任务时我可能会直接编辑表而不是使用表单。

#6

I realise this is dead but google brought me here while i was looking so thought I would add to it:

我意识到这已经死了但谷歌把我带到了这里,而我正在寻找我想加入它:

I had this problem myself (Access 2010) and found a decent answer here: http://www.vbforums.com/showthread.php?503841-How-to-Convert-MonthName-to-Value(Microsoft-access-2003)

我自己遇到了这个问题(Access 2010),并在这里找到了一个不错的答案:http://www.vbforums.com/showthread.php?503841-How-to-Convert-MonthName-to-Value(Microsoft-access-2003)

So what I did was have a Query which pulled out the DISTINCT months from my table. Then in the design view i added another column with MonthNo: Month(CDate("1 " & [Month]))and sorted the query on this column

所以我所做的是有一个查询,它从我的表中删除了DISTINCT个月。然后在设计视图中我添加了另一个包含MonthNo:Month(CDate(“1”和[Month]))的列,并对此列进行了排序

hope this helps someone if not the OP.

希望这有助于某人,如果不是OP。

#1