Access Date Interpretation

[scottyjohn]

Hi all,
I have an Oracle view setup which is defined as follows...

CREATE OR REPLACE VIEW V_CALLTYPE_CDR_V2 ( EMPID,
STARTD, STARTT, ENDD, ENDT,
BRAND, PRODUCT, DNIS, CLID,
DIRECTION, CONNID, TERM, PROG, TT, HT, ACWT )
AS SELECT EMPID, TO_DATE(TO_CHAR(STARTT, 'DD/MM/YYYY'),'DD/MM/YYYY'),
TO_CHAR(STARTT, 'HH24:MI:SS'),
TO_DATE(TO_CHAR(ENDT,'DD/MM/YYYY'),'DD/MM/YYYY'),
TO_CHAR(ENDT,'HH24:MI:SS'),
BRAND, PRODUCT, DNIS, CLID, DIRECTION,
CONNID, TERM, PROG, TT, HT, ACWT
FROM CALLTYPE_CDR

This works ok in toad but our users use MS Access as a front end to query the data. Now when putting dates in the select criteria of the query, Access appends a # before and after the date, which I assume means that its seeing it as a date. However the query returns spurious values as if the criteria is not registering. I am using the Microsoft Oracle ODBC driver. Any ideas?

John
ski_69@hotmail.com

 

[Craig0201]

Date format mismatch? You're sending the date in UK format.

 

[scottyjohn]

Can you elaborate? In the view definition the date is specified in UK format so I assumed that typing
Between #29/05/2005# and #30/05/2005# into the criteria field on the query design would give me only results for those two days. I cant see anywhere to alter the date format in Access?

John
ski_69@hotmail.com