Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

Question

I have a column titled DateOpen which lists the date files were opened in d/m/yyyy format. I want to split the entries in that column into new columns titled MonthOpen, DayOpen and YearOpen while keeping the fields ordered by the primary key column titled FileNumber. Thanks in advance.

Answer 1

Select FileNumber, 
    DateOpen as DayOpen, 
    Month(#DateOpen#) as MonthOpen, 
    Year(#DateOpen#) as YearOpen
From [file]
Order by FileNumber

I assume by day you just want to show the date you have from the day open field. Month(DateOpen) will give you the month number. You weren't specific about what you were looking for in this field, so I assume this is ok. Order By FileNumber ensures you maintain the order of the primary key field.

Answer 2

I assume you are running some type of query. You need to set the order by property. In SQL this would look something like the below example.

Select FileNumber, DayOpen, MonthOpen, YearOpen
From file
where <some condition>
Order by FileNumber desc