Linking Oracle Tables in Access (Problem Field)

[hoopfiend]

I have an Oracle table that I have linked to an ACCESS database, where the
date/time field value only shows up as "OLE Object". How do I resolve this
issue to actually bring in the date value it's supposed to be? The other
fields are characters or numbers and are fine.

 

[Lynn Trapp]

Have you verified that the field in the Oracle table is, in fact, of Date
data type?

 

[hoopfiend]

Yes, I had the Oracle DBA verify this. His solution was to somehow create a
view that allows me to query the table using that field as a criteria for the
subset. Running the query requires that the user enter a single date in the
pop-up box. However, some of the queries and reports that I am converting to
use with the new source (the linked table) require date ranges. So, that
does not work so well.

 

[Lynn Trapp]

You ought to be able to create 2 parameters to query for your date range.

 

[hoopfiend]

You would think. I think it's just a case of a square peg in a round hole.
I assumed changing the source from SQL to Oracle would be fairly painless.
However, there is something odd about that one field. Even the 'view'
requires that the input be in a 'DD-MMM-YY' format.

Thanks for helping me brainstorm, though.

 

[Lynn Trapp]

Formatting dates in Oracle so that the database can understand what you are
talking about can be a pain. You might try having your DBA create a view
with the Date fields formatted as Varchar2 and then you convert them to
dates using Access functions.

 

[Smartin]

I have an Oracle table that I have linked to an ACCESS database, where the
date/time field value only shows up as "OLE Object". How do I resolve this
issue to actually bring in the date value it's supposed to be? The other
fields are characters or numbers and are fine.

FWIW When I'm looking at dates in a linked Oracle table I have to wrap
the field in CDate():

WHERE CDate(MyLinkedOracleTable.OracleDateField) = #1/2/2006#

 

[hoopfiend]

It looks like the ORACLE field is actually TIMESTAMP versus DATE/TIME. That
seems to be the problem.