can you pass a function to a sql statement?

[Danny]

Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(n ame) 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

[Rich P]

Re: can you pass a function to a sql statement?

Yes. Make sure that "getdepartm ent" 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
[color=blue][color=green]
>>[/color][/color]
Hi

can I do this?
db.execute "insert into newtable (name, address, department) select
source.name, source.address, getdepartment(n ame) 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!

[John Winterbottom]

Re: can you pass a function to a sql statement?

"Danny" <dannywork5@hot mail.com> wrote in message
news:yH7qc.2713 3$MH.10716811@n ews4.srv.hcvlny .cv.net...[color=blue]
> Hi
>
> can I do this?
> db.execute "insert into newtable (name, address, department) select
> source.name, source.address, getdepartment(n ame) 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[/color]
not[color=blue]
> found or somethng.
>[/color]

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

[Danny]

Re: can you pass a function to a sql statement?


"John Winterbottom" <assaynet@hotma il.com> wrote in message
news:2gsq68F6f9 2pU1@uni-berlin.de...[color=blue]
> "Danny" <dannywork5@hot mail.com> wrote in message
> news:yH7qc.2713 3$MH.10716811@n ews4.srv.hcvlny .cv.net...[color=green]
> > Hi
> >
> > can I do this?
> > db.execute "insert into newtable (name, address, department) select
> > source.name, source.address, getdepartment(n ame) 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[/color]
> not[color=green]
> > found or somethng.
> >[/color]
>
> why not use a subquery instead and do away with those nasty, proprietary[/color]
VBA[color=blue]
> 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
>
>[/color]

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

Thank you !!

Danny