473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calling user-defined function from query

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
7 60432
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
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
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
>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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Earl | last post by:
If calling an exe that accesses a database from an ASP page, does the Provider string to connect to the database from inside the exe need to have the user as being "IUSR..." or something since it...
2
by: b.bardugo | last post by:
Hi all, I have a C# user control (a little app I made to display video). In this code I am using a third party .dll, which is written in C++ (un-managed). I am tring to put this control on a web...
0
by: Michael Brandt Lassen | last post by:
Hi gurus This problem is about calling Web services secured by Forms Authentication from Windows Forms user controls embedded in HTML. Using the object tag I’ve managed to include a Windows...
10
by: Sorin Dolha [MCSD .NET] | last post by:
I would like to start a process from C# code as another user. The C# code is executed as the ASPNET user because it relies in a Web Page class, and I would like that the process will run as another...
10
by: George G. | last post by:
Hi there, I am busy writing a new asp.net application and I am reusing some of my existing asp functions and methods in a user control. I need access to session, request and response in some of...
3
by: Patrick Olurotimi Ige | last post by:
How can i send LOGON_USER or User.Identity.Name to the Database? I'm working on a survey which is intranet based with Windows Authentication. I can get the current user by calling LOGON_USER or...
4
by: louise raisbeck | last post by:
Resending this as own topic as didnt get answer from original. Would be grateful for a response from anyone that knows. Thanks. Hi there, I found your post really helpful..but i wondered if, once...
1
by: Enemaerke | last post by:
Hi I've searched through this newsgroup but have been unable to find something to answer my question so I'd better go ahead and post it We are currently developing a web service for processing...
4
by: =?iso-8859-1?q?Eir=EDkur_Fannar_Torfason?= | last post by:
I'm wrestling with a problem that I'm hoping someone can help me with. I have a web application written in VS.2003 and running on version 1.1 of the .NET Framework on XP pro and Windows server...
4
by: =?Utf-8?B?QXZhRGV2?= | last post by:
ASP.Net 2. We are migrating to Windows 2008 64 bit Server with IIS 7 from Windows 2003 32 Bit with IIS 6. A few library classes we wrote uses impersonation in code like explained in this...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.