[AccessD] Date/Time Conversion

On 6 Nov 2005 at 12:38, Robert Gracie wrote:

> 
> 
> I have a table that has a date field that the input has been the date & the
> time (Now()). I'm trying to build a report based on the results of a query
> that the user inputs the date.
> 
> I have tried formatting the field by using "TransDate:
> Format([DateOFMovement],"mm/dd/yyyy")", with the criteria "Between
> [Start Date ie: 10/05/2005] And [End Date  ie: 10/06/2005]". 
> 
> So if the users input is 01/01/2005 for the first date and 01/10/2005
> for the second, the query results include 2004 records also, but with-in the
> month and day criteria... 
> 
> If I try to sort the results is ascending order on the date, it will
> only sort on the month & day..
> 

Others have pointed out that you should filter on the underlying date and 
format in the report.

There is one other "gotcha" to watch for cases like this where you are 
filtering dates on a date/time record.

When using a criterion of Between 01/01/2005 And 01/10/2005:

If you are select on a field where only dates have been entered (using the 
Date() function)  it will return ALL records for 01/10/2005.

If the field contains a date and time (filled using the Now() function) it 
will return NO records for 01/10/2005.

The reason is because the criterion date "01/10/2005" is treated as 
"01/10/2005 00:00:00".   A time part in any record for 01/10/2005 makes the 
entry greater than the criterion and it is skipped.

-- 
Stuart