468,539 Members | 1,628 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

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

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!
Nov 12 '05 #1
1 1915
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!

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Alfonso Esteban Gonzalez Sencion | last post: by
2 posts views Thread by egoldthwait | last post: by
12 posts views Thread by zwasdl | last post: by
10 posts views Thread by WannaKatana | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.