Calling a function built in access in a query to Oracle through ODBC

[Peter Monica]

I am trying to calculate distance from a point entered by a user usng
a function in Access in a query that calls tables in an Oracle
database as well as in Access. I am getting the following message:

Undefined function 'Distance' in expression.

Here is the query:

SELECT people.first_name
, jobs.job
, job_types.job_type_description
, Distance(DLookUp("latitude","ZIP_CODES","zip = " &
[Forms]![testform]![Combo4]),DLookUp("longitude","ZIP_CODES","zip = "
& [Forms]![testform]![Combo4]),[addresses]![LATITUDE],[addresses]![LONGITUDE],True)
AS Miles
FROM (job_types INNER JOIN (people INNER JOIN jobs ON people.people_id
= jobs.people_id) ON job_types.job_type = jobs.job_type) INNER JOIN
addresses ON people.people_id = addresses.people_id
WHERE (((specialty_mapping.panel_specialty)=[Forms]![testform]![Combo0]));

OK, so the table names have been changed, but here's where each
resides:
people - Oracle
jobs - Oracle
addresses - Oracle
job_types - Access
zip_codes - Oracle

Also, the function I am using (Distance) does work, and is a Public
Function in the database.

Any ideas?

Thanks!

[DFS]

Looks like your latitude and longitude arguments aren't syntaxed correctly.
Also I see you're referencing Zip Codes using a number syntax. Zip Codes
should be stored and used as if they were text, because some start with 0.

Try:

Distance(
DLookUp("latitude","ZIP_CODES","zip = '" & [Forms]![testform]![Combo4] &
"'"),
DLookUp("longitude","ZIP_CODES","zip = '" & [Forms]![testform]![Combo4] &
"'"),
[addresses]![LATITUDE],
[addresses]![LONGITUDE],
True)
AS Miles
"Peter Monica" <pe**********@concentra.com> wrote in message
news:42************************@posting.google.com ...

I am trying to calculate distance from a point entered by a user usng
a function in Access in a query that calls tables in an Oracle
database as well as in Access. I am getting the following message:

Undefined function 'Distance' in expression.

Here is the query:

SELECT people.first_name
, jobs.job
, job_types.job_type_description
, Distance(DLookUp("latitude","ZIP_CODES","zip = " &
[Forms]![testform]![Combo4]),DLookUp("longitude","ZIP_CODES","zip = "
& [Forms]![testform]![Combo4]),[addresses]![LATITUDE],[addresses]![LONGITUDE],
True) AS Miles
FROM (job_types INNER JOIN (people INNER JOIN jobs ON people.people_id
= jobs.people_id) ON job_types.job_type = jobs.job_type) INNER JOIN
addresses ON people.people_id = addresses.people_id
WHERE (((specialty_mapping.panel_specialty)=[Forms]![testform]![Combo0]));

OK, so the table names have been changed, but here's where each
resides:
people - Oracle
jobs - Oracle
addresses - Oracle
job_types - Access
zip_codes - Oracle

Also, the function I am using (Distance) does work, and is a Public
Function in the database.

Any ideas?

Thanks!