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