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 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.
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
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
>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.
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.
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.
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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
| |