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

Calling user-defined function from query

P: n/a
I know this has been covered before but being a beginner at SQL, and
only marginally better at VBA, I'm struggling. All I wish to do is call
the following function which is in "Microsoft Office Access Class
Objects":

Function GetPropertyCode() As String

GetPropertyCode = PropertyCode

End Function

And I want to call it in the following SQL query in access as a
criteria to set owner code = to it.

SELECT Herd_Animals.Owner, Herd_Animals.Animal_Tag, Herd_Animals.Grade,
Herd_Animals.Sex_Code, Herd_Animals.Date_Of_Birth,
Herd_Animals.Group2_Code INTO QTable1
FROM Herd_Animals
WHERE (((Herd_Animals.Owner)=GetPropertyCode()) AND
((Herd_Animals.Date_Of_Birth)>=[Forms]![Form1]![TxtStartDate] And
(Herd_Animals.Date_Of_Birth)<=[Forms]![Form1]![TxtEndDate]));

I have had a feeble attempt at this previously as well as checked on
the web but struggling.....

Cheers

Reg

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Reg,

this is a grossly simplified version of your query, but it should be
enough to point you in the right direction.

My simple function:

Public Function MyFunction() As String
MyFunction = "bike"
End Function
The query calling the Function:

SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE (((Products.ProductName)=MyFunction()));

Note the =FunctionName() <== pair of parentheses.

Nov 13 '05 #2

P: n/a
Pietlin,

Thanks for all your help. If you compare your example and what I showed
up top, I believe it is the same. However, I have worked out what the
problem is (I think). I had the function in my form class, not in a
module of its own. When I moved it to a module, it worked when I set
the value to "DGS". Now I just need to work out how to pass variables
between the function and the Form1 class.... thanks again

Nov 13 '05 #3

P: n/a
Regnab wrote:
Pietlin,

Thanks for all your help. If you compare your example and what I showed
up top, I believe it is the same. However, I have worked out what the
problem is (I think). I had the function in my form class, not in a
module of its own. When I moved it to a module, it worked when I set
the value to "DGS". Now I just need to work out how to pass variables
between the function and the Form1 class.... thanks again


Wait, you're passing values INTO your function? You pass values into a
function like this:

Function MyFunction (MyArgument As String) As String
' say you want to reverse the string...
MyFunction=StrRev(MyArgument)
End Function

Call this from a query...
SELECT MyField, MyFunction([MyTable]![MyField]) As SeeMyFunction
FROM MyTable...

I could be totally missing the question. If so, give me a really short
example and explain a little more.

HTH.
Pieter

Nov 13 '05 #4

P: n/a
>From the start :). I have vba coding that runs this query once for each
property selected ie selecting DGS and CC (property codes) would mean
the query is run twice. Now each time it is run, I need to pass the
relevant property code from the code to the query in access. This
property code is in a variable (in the code) called Property Code. To
get this from the query, I call the function - by typing
"GetPropertyCode()" in the criteria of "Owner Code" and allowing access
to generate the sql.

To test if the function calling was working, I set the function like
this:

Function GetPropertyCode() As String
GetPropertyCode = "DGS"
End Function

And she worked.... so now I'm just trying to get it so I can replace
"DGS" with a variable from my form code... it just occured to me (bit
messy though) - could I call a function residing in the form code from
the module to get the variable into that function?? :)... ahh, I'm a
terrible programmer.

Nov 13 '05 #5

P: n/a
Whoa. I'm getting confused now. What *exactly* are you trying to do?
I mean, there's just got to be an easier way of accomplishing what you
want without all this hassle.

One way of doing this the easy way is to create a table or query that
contains/selects the values you want to pass to this function. The
nice thing about this is that you can create a table of something like
Table(TextField, YesNoField) and then you can just loop through the
records in that table and pass them to some other function(s) if you
want.

So back up a bit. if it's easy to loop through a recordset based on a
table or query and then just pass those values to some function(s).

Okay, so where to go from here ... what are you trying to do? I'm
getting really confused here.

Nov 13 '05 #6

P: n/a
rkc
Regnab wrote:
Pietlin,

Thanks for all your help. If you compare your example and what I showed
up top, I believe it is the same. However, I have worked out what the
problem is (I think). I had the function in my form class, not in a
module of its own. When I moved it to a module, it worked when I set
the value to "DGS". Now I just need to work out how to pass variables
between the function and the Form1 class.... thanks again


Why can't you just run the function before the query is run as a first
step in whatever process is used to run the query? That way you can
use the function to return a value, place the value in a control on the
form and reference the control in the query the same way you do with
startdate and enddate.
Nov 13 '05 #7

P: n/a
Yeah, that was one way I thought of doing it, but I thought the
function method was the easier, normal way of doing it.... clearly I
was wrong on that one ;). Anyways, I took your advice Pieter and passed
the values through a table which has got it working a treat. Thank you
kindly. Just on another topic, is there a quicker way of cycling
through/counting the number of values in a user-defined array?? the
questions are endless :) - thanks for your patience...

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.