ODBC Query / Between Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FBxiii - 12 Nov 2007 15:59 GMT

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.

Reply to this Message

Duane Hookom - 12 Nov 2007 16:14 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> This may sound a little a confusing, but here goes...
>
[quoted text clipped - 25 lines]
> Cheers,
> Steve.

Reply to this Message

FBxiii - 12 Nov 2007 16:19 GMT

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

> 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
[quoted text clipped - 31 lines]
> > Cheers,
> > Steve.

Reply to this Message

FBxiii - 12 Nov 2007 16:22 GMT

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

> I said it was confusing!
>
[quoted text clipped - 39 lines]
> > > Cheers,
> > > Steve.

Reply to this Message

Duane Hookom - 12 Nov 2007 17:01 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> Sorry, it is an Oracle 7.3 database i am linking to...
>
[quoted text clipped - 41 lines]
> > > > Cheers,
> > > > Steve.

Reply to this Message

FBxiii - 13 Nov 2007 09:48 GMT

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.

> 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
[quoted text clipped - 48 lines]
> > > > > Cheers,
> > > > > Steve.

Reply to this Message

Duane Hookom - 13 Nov 2007 14:19 GMT

I would always include the times.

Signature

Duane Hookom
Microsoft Access MVP

> Thanks for the reply.
>
[quoted text clipped - 58 lines]
> > > > > > Cheers,
> > > > > > Steve.

Reply to this Message