[AccessD] Date/Time Conversion

Robert Gracie robert at servicexp.com
Tue Nov 8 06:39:21 CST 2005


Lambert,
 Thank you very much, had no idea...  


Robert Gracie
www.gbsysnow.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan,
Lambert
Sent: Monday, November 07, 2005 10:46 AM
To: 'Access Developers discussion and problem solving'
Cc: Robert Gracie
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list