Short dates - why do they give time?

[JohnGo]

I'm trying to define a short date field in access but it gives me headaches.
The short date also displays time, this gives problems in my group by queries as I want to group on date instead of date and time

Is there any datatype in Access to really store the date and not date+time?

I already checked my system settings as they might be the cause, but it didn't help me..

 

[JohnGo]

I was using a Now() function as default value and changed it in date.
Within an experimental table everything looks fine now.

But my current table was already spoiled with now data so I ran an update query to get all dates to 00/00/0000. That worked, but when inserting new dates Access still saves the time within the datestamp.

 

[JohnGo]

Ok this sounds rather stupid but I was busy in the wrong table which didn't relate to my query.
When setting Now() to date() in the right table everything looks fine..
sorry for bothering

 

[mattP]

Pat,

Sorry for what is probably a basic question, but where would you use the DateValue expression, I want my query to report the date only and it already has criteria, on the date entered, that only collates the last 12 months worth of data.

I want the query to have a 2nd column that give me a month value, based on the date (so for 12/01/05 the 2nd column shows Jan-05) however the lookup is not working due to the time elements in the Date entered field.

Any help appreciated.

MattP

 

[The_Doc_Man]

In a query, include a field that is a function of your date/time field. Look up User-defined time formats in Help to see how this works.

You want the query grid of the sort query to contain something SIMILAR - but perhaps not identical - to

SortDate: Format$( [MyDateTimeField], "yyyy-mm-dd" )

Then sort on this field - or sort/group-by this field.

 

[mattP]

The Doc Man

Thanks for your help, it worked, though I moved the format around to show "mmm-yy". it worked perfectly.

many thanks for your help

MattP

 

[The_Doc_Man]

You are welcome. And as I suggested ... similar but not necessarily identical.