[AccessD] Date/Time Conversion

Good, you got rid to the "Format()" use, but there's still one thing waiting
to catch you. When you use Between in a criteria like

Between [Start Date ie: 10/05/2005] And [End Date  ie: 10/06/2005]".

You have to be aware that just a bare date like 10/05/2005 means Midnight on
that date, and because your date field includes the time part, the second
date in the query can cause you problems as it too only goes as far as
midnight. So if the second date entered for the query criteria was
10/06/2005 your query would not show results for dates later than 10/06/2005
12:00:00 AM. e.g. the date 10/06/2005 08:00:00 AM would not be returned by
the query as it is after midnight on 10/06/05.

The fix is easy. Just change the second date prompt like this..

Between [Start Date eg: 10/05/2005] And Cdate([End Date  eg: 10/06/2005])
+#11:59:59 PM#


By adding the time portion (one second prior to midnight) to the end date
you will get results covering the whole 24 hour period of the end date.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert Gracie
Sent: Sunday, November 06, 2005 2:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Date/Time Conversion


David,
Ha, Using "format" in the query is what was screwing it up.  

Thanks!!

Robert Gracie
www.gbsysnow.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Sunday, November 06, 2005 1:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Date/Time Conversion

I don't think you need the slashes in the date in the format function.

I was going to ask why are you even using the format function? I would just
put the criteria in the field and format the reports displayed field.

HTH
David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Robert Gracie
Sent: Sunday, November 06, 2005 9:39 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Date/Time Conversion




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..

What am I doing wrong??


Robert Gracie
www.gbsysnow.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com