ODBC Query / Between Statement

[FBxiii]

This may sound a little a confusing, but here goes...

I have a query that is run against a database using an ODBC link.

The table in question has a field called CREATED_DATE_TIME and is in the
format DD/MM/YYYY HH:MM:SS

For years now, I have used a report to bring back records where the
CREATED_DATE_TIME Between #01/10/2007# and #31/10/2007# (UK format). This
has seemed to work fine.

I have come to run the report this month and it is missing some records from
the 31/10/2007. If I remove the between criteria from the report, it returns
the records, showing the CREATED_DATE_TIME as 31/10/2007!!

If I put the time into the date parameters (i.e. 31/10/2007 00:00:00 -
31/10/2007 23:59:29) this brings back the required fields also! Or if I ask
for records between 31/10/2007 and 01/11/2007, this works aswell.

Is there an explanation to this? Why would the report become unstable after
all this time?

My gut instinct is that there is a problem with the linked table itself, but
I may be missing something.

Can anyone help?

Cheers,
Steve.

 

[Duane Hookom]

I would be surprised if the query ever worked properly before if the date on
the last day of the month also included a time. We don't know what your
actual source database is (SQL Server, ORACLE,...) but in Access and SQL
Server, specifying 31-JAN-2007 as the maximum date/time value would never
return 31-JAN-2007 12:05 AM.

 

[FBxiii]

I said it was confusing!

The query has never included times, just the first and last day of the
month. I have put times in as I have been trying to work out what the
problem may be (as well as removing the date criteria totally).

I thought if it was a Date & Time field, just the date would suffice...

 

[FBxiii]

Sorry, it is an Oracle 7.3 database i am linking to...

 

[Duane Hookom]

I don't think that any major database would automatically truncate a date
with time to just a date. I would expect you would have to apply some other
functionality such as DateValue() to remove the time portion of a date/time
value.

You should be go back and query previous months.

 

[FBxiii]

Thanks for the reply.

So I should actually include times in the criteria then? It has always
seemed to work, but maybe it didnt...

Thanks for the advice, I will check last months report.

Cheers,
Steve.

 

[Duane Hookom]

I would always include the times.