Setting query Criteria From a Function

[1starr]

I having attempting to get my query to get criteria input from a Public Function in order to create the output for a report. In the Public Function I have it set up like this:
Option Compare Database

Option Explicit

Dim strDom As String

Public Function FindDomain()
FindDomain = strDom

End Function

So what I have done is try to gather the data from FindDomain() and have the quey on look for that data. Something like this:

SELECT [CERT June].Category, Status.Weight, Status.Status, Sum([CERT June].Count) AS SumOfCount, [CERT June].Domain
FROM [CERT June] RIGHT JOIN Status ON [CERT June].Status = Status.Status
GROUP BY [CERT June].Category, Status.Weight, Status.Status, [CERT June].Domain
HAVING ((([CERT June].Domain)=FindDomain()))
ORDER BY [CERT June].Category, Status.Weight;

What am I doing wrong, Please help....

 

[AncientOne]

I don't understand what your function is supposed to do. How do you call this function? Where does strDom come from? Why can't you just send strDom to your query as a parameter?

 

[EMP]

You haven't stated what the problem was when the query was run.


Assuming the public function is placed in a Module, change:
Dim strDom As String

to:
Public strDom As String

 

[raskew]

And either way, FindDomain = "" because strDom = "".

The function doesn't do anything and strDom hasn't been assigned a value.

 

[1starr]

The value of strDom is assigned when the user open the form to enter data for certain Dormain. The user needs to print out a new report after they had entered their data which includes all the previous data entered and to keep them from having to enter the strDorm data again all they would have to do is click the print report button once they had finish.

 

[1starr]

Thanks, for your help but all of sudden it started working. I don't know what the bug was cause yesterday I first step thru it and it seem that the flow of data for strDom wasn't flowing correctly and them when I came back to it today, it worked with out a problem. There must had been some kinda bug or it might be because I reboot my system that cause it to work correctly. Again thank you for your assistance.

 

[Pat Hartman]

You need to be aware that under certain error conditions, global variables loose their value. In a db under development this can be really annoying. An alternative method is to use a hidden form that you open at db startup. It will retain values even if errors arise.