Order by Date Only

[Tieval]

Hi,

I have a set of data which is imported and in the past has been sorted by date, this suits my purposes perfectly as the data is date format only with no time (it imports as a 00:00:00 time).

As the data has been imported in a correct order I could sort it by date and then primary key.

ORDER BY qryDone.SDate, qryDone.ID;

The data I receive is now changing to date and time and what I want to do is optionally sort by date and time or sort by date and primary key.

Basically, how do I sort by just the date part of the date and time?

 

[Minty]

DateValue(SDate) will return the date only portion of your date field.

 

[Ranman256]

You dont need to change anything. Even with the time, it still sorts the same.
But if you dont want to SEE the time, in the query design,
put cursor in the date field on the grid
set property: FORMAT = short date.

 

[Gasman]

What is the need? Just show the date part as it will still be sorted just within the day as well?

 

[isladogs]

One possible complication will be using date ranges in queries
E.g Between StartDate and EndDate.
The issue you may have is getting the values on the EndDate as these won't include anything during that day.
Solution: Use EndDate +1 in your query filter criteria

 

[Tieval]

Many thanks Minty, worked a treat.

As to the point, I have a number of different sources of data and may want to look at them together or separately.

I can sort all data by date and time (where the different sources intermingle) or by date and individual data source.

 

[Tieval]

Taking this a stage further, how do you put options in a sort order?

ORDER BY If forms!mainf!chk59 = -1 then SDate, ID
If forms!mainf!chk59 = 0 then DateValue(SDate);
If IsNull (forms!mainf!chk59) then DateValue(SDate);

Any help would be greatly appreciated