By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

can you pass a function to a sql statement?

P: n/a
Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(name) from source"

i made a getdeparment function that looks up another table and finds the
deparmtent based on name.

This is failing and giving me an error that getdeparment is unefined or not
found or somethng.

Thanks in advance
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Yes. Make sure that "getdepartment" is a function and not a sub, and
make sure that it is in a standard code module not a form module.
Additionally, it sounds like you could use the DLookUp function instead
of your User-Defined function (check help files). To make sure that a
function works (Access function or User-Defined function) I always try
it out in a select query first. If the function works in the Select
query then you can use it in an action query.

Rich

Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(name) from source"

i made a getdeparment function that looks up another table and finds the
deparmtent based on name.

This is failing and giving me an error that getdeparment is unefined or
not
found or somethng.

Thanks in advance
<<


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

P: n/a
"Danny" <da********@hotmail.com> wrote in message
news:yH*********************@news4.srv.hcvlny.cv.n et...
Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(name) from source"

i made a getdeparment function that looks up another table and finds the
deparmtent based on name.

This is failing and giving me an error that getdeparment is unefined or not found or somethng.


why not use a subquery instead and do away with those nasty, proprietary VBA
functions :)

insert into newtable(name, address, department)
select s1.name, s1.address,
(
select s2.name
from source2 as s2
where s2.pkField = s1.fkField
) as department
from source as s1
Nov 12 '05 #3

P: n/a

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2g************@uni-berlin.de...
"Danny" <da********@hotmail.com> wrote in message
news:yH*********************@news4.srv.hcvlny.cv.n et...
Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(name) from source"

i made a getdeparment function that looks up another table and finds the
deparmtent based on name.

This is failing and giving me an error that getdeparment is unefined or not
found or somethng.


why not use a subquery instead and do away with those nasty, proprietary

VBA functions :)

insert into newtable(name, address, department)
select s1.name, s1.address,
(
select s2.name
from source2 as s2
where s2.pkField = s1.fkField
) as department
from source as s1


Thanks for all of your help, wow I did not know I could do these things.
I look forward to trying.

Thank you !!

Danny
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.