Using Access Query to Selectively Extract Oracle Records 1

[LevelThought]

I am using a Access query to extract records from an external Oracle database. One of the criteria that I would like to use to selectively extract data is a "Date_Updated" field within the Oracle table.

The Date_Updated field in the Oracle table have a date and time component. Using "Format(Date_Updated),"m/d/yy")" within Access QBE along the "Field" row enables the display of the date without the time component.

However, I am not able to selectively display records that have been updated today or for any particular date (say July 15, 2005). Therefore, I receive all records within the Oracle database. For example, if I enter "Date()" along the criteria row, I do not receive the Oracle records that have been updated today.

What should the "where" portion of my Access query look like if I want to display only the Oracle records that were updated today?

 

[GingerR]

If you enter Date() in the criteria row, what do you get back? Are there really records there for "today"?

What is your query's SQL statement? What is some sample data? Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

WHERE Int(Date_Updated)=Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

PHV,

Upon entering "Int(Date_Updated)=Date()" within the Access QBE along the row "Criteria", I receive an error stating

Date type mismatch in criteria expression.

I will try entering it as Int(Date())

Also, how should this be modified if I wanted to extract records for a particular date, for example, July 15, 2005?

 

[PHV]

I personally never use the query grid but the SQL view thus my suggestion was the criteria in SQL code.

WHERE Int(Date_Updated)=#2005-07-15#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

PHV,

The error persists. I am using Access 2003 trying to extract from an Oracle version 8.1.7 database.

I assume that you are trying to convert date to an integer by specifying "int." However, it appears that the time component is not eliminated by specifying "int."

 

[JerryKlmns]

LevelThought

Date_Updated is a date field?
Dates are numbers formated for humans
ie 38568,0020833333 = 04/08/2005 00:03
The integer part is the date and the rest is time

Would you give some values you are getting from your Oracle?

 

[GingerR]

instead of just FORMAT, use this (substitute in your field name):

CDate(Format([AS_LOGON_TIME],"mm/dd/yy"))

Criteria for today: Date()
Criteria for other days, make sure to surround with #:

#7/1/2005#

works fine

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[LevelThought]

Ginger,

Your example appears to be the solution! However, the processing time was over 30 minutes!!

Using the Access SQL below, I returned 1195 records that have "5/28/2005" in the "date_updated" column.

SELECT Format(ACCOUNT_PAYMENT_DETAIL.DATE_UPDATED,"m/d/yy") AS EPD_DateUpdated, ACCOUNT_PAYOR.CONTRACT_ID,

ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO, PATIENT_ENCOUNTER.TOTAL_CHARGES,

PATIENT_ENCOUNTER.EXPECTED_REIMBURSEMENT, PATIENT_ENCOUNTER.DATE_BILLED, ACCOUNT_PAYOR.TOTAL_PAYMENTS,

PATIENT_ENCOUNTER.NONCOVERED_PT_CHARGES, PATIENT_ENCOUNTER.NONCOVERED_WO_CHARGES,

EVIEW_TRANSACTION_DETAILS.ADJUSTMENT_AMOUNT, EVIEW_TRANSACTION_DETAILS.TRANSACTION_CODE,

ACCOUNT_PAYOR.RANK, CDate(Format([ACCOUNT_PAYMENT_DETAIL].[DATE_UPDATED],"m/d/yy"))
FROM ((ACCOUNT_PAYMENT_DETAIL INNER JOIN ACCOUNT_PAYOR ON ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO =

ACCOUNT_PAYOR.ENCOUNTER_NO) INNER JOIN EVIEW_TRANSACTION_DETAILS ON

ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO = EVIEW_TRANSACTION_DETAILS.ENCOUNTER_NO) INNER JOIN

PATIENT_ENCOUNTER ON (ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO = PATIENT_ENCOUNTER.ENCOUNTER_NO) AND

(EVIEW_TRANSACTION_DETAILS.ENCOUNTER_NO = PATIENT_ENCOUNTER.ENCOUNTER_NO) AND

(ACCOUNT_PAYOR.ENCOUNTER_NO = PATIENT_ENCOUNTER.ENCOUNTER_NO)
GROUP BY Format(ACCOUNT_PAYMENT_DETAIL.DATE_UPDATED,"m/d/yy"), ACCOUNT_PAYOR.CONTRACT_ID,

ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO, PATIENT_ENCOUNTER.TOTAL_CHARGES,

PATIENT_ENCOUNTER.EXPECTED_REIMBURSEMENT, PATIENT_ENCOUNTER.DATE_BILLED, ACCOUNT_PAYOR.TOTAL_PAYMENTS,

PATIENT_ENCOUNTER.NONCOVERED_PT_CHARGES, PATIENT_ENCOUNTER.NONCOVERED_WO_CHARGES,

EVIEW_TRANSACTION_DETAILS.ADJUSTMENT_AMOUNT, EVIEW_TRANSACTION_DETAILS.TRANSACTION_CODE,

ACCOUNT_PAYOR.RANK, CDate(Format([ACCOUNT_PAYMENT_DETAIL].[DATE_UPDATED],"m/d/yy"))
HAVING (((ACCOUNT_PAYOR.CONTRACT_ID) Not In

("GVOP","GVCC","GVTU")) AND ((EVIEW_TRANSACTION_DETAILS.TRANSACTION_CODE) In ('48003','48004','48005')) AND

((ACCOUNT_PAYOR.RANK)=1) AND ((CDate(Format([ACCOUNT_PAYMENT_DETAIL].[DATE_UPDATED],"m/d/yy")))=#5/28/2005#))
ORDER BY ACCOUNT_PAYOR.CONTRACT_ID, ACCOUNT_PAYMENT_DETAIL.ENCOUNTER_NO;


The initial plan was to combine the results of this query ("Payment_Detail" table) with the results of two other queries via a "union" query. Consequently, I will have all records that were updated today in the Oracle database for the three tables,"Payment_Detail," "Encounter_Pay" and "Customer" tables.

Then, bind the results to a form and subsequently, update the calculated fields within my Access table using the after update event on the form.

Any additional insight as to the logic or the steps in updating calculated values in a table from a Access form is appreciated.

 

[GingerR]

Typically with Oracle or SQL Server data that someone else owns, I import in each day (at 3 or 4 am so as not to disturb anyone) the data that I want. Then my users have fast access to the data (from local Access tables). Also, if it takes a long time to get the data, i don't care because I'm asleep. Maybe that is something for you to consider, although your process of viewing records that were updated "Today" might not allow that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html