Order by date with MS Access

How can I "order by" a field containing a date/time with MS Access? Think I need to insert a kind of "sysdate" but cant find any info about it.

Freddy

Order by field ASC | DESC

I did mean how to insert some kind of sysdate in MS Access. Must use a date in db-format if I want to order by a date.

Are you inquiring about how to create a date field so that you can order by it? What version of MS Access are you using? You should be able to add a date/time field and select the format from the Design View.

If you have the field to hold the date time

In your insert statement use

insert into tbl_table (thedate) values (#'&now()&'#)

This will give you table the date and time it was inserted

The when you select

select * from tbl_table order by thedate

Thanks, spc197!

That was it.

Didn`t know that I could use an asp-date for this, but now i do :-)

even better, use the internal date that Access provides --

insert into tbl_table (thedate)
values ( now() )

or date() if you don't want the time portion

rudy

I'm pulling information from a phone system via an ascii file and then putting it in a database. It is an Access database. I have one field with the date and another with the time.

When I report based on hour it sorts them as follows:

1
10
11
12
13
2
3
4
5
6
7
8
9

I'd like the report I'm generating to be in proper hour order:

00
01
02
03
04
05
06
07
08
09
10
11
12
13

Trying to create my own date/time value in my query doesn't seem to do it. Before I start going crazy changing the data and the import process is there a way to do this in my query or will it work if I make a new field with both date and time?

Have you defined the time-field as date/time? I think you have to do that to make access interprete the values as date/time. Then you should be able to sort them right...

Freddy

Yes it is set as a date/time field and I specified the format be short time XX:YY which matches the data in. However, I need to group by the hour XX and so I used Left in my SQL. I switched to using DatePart('h',entryTime) and now it is working...