Can I use a function with arguments in a query?

[Mabjro]

How can I use a function with arguments in a query?

I’m trying to use a function w/arguments in a query;

“Select * FROM mytable Where myfield = ReturnMyVar() “ produces the following error.

Wrong number of arguments used with function in query expression
‘(((mytable.myfield) = ReturnMyVar()))’


The problem is that the ReturnMyVar function receives a variable from anther procedure in a Call Tree.

The rest of the story;


On the GotFocus event of a field called “Description,” I have the following procedure;

Public Sub Description_GotFocus()
myvar = Me.Description.Value
‘passes the value of myvar to the funtion ReturnMyVar()
Call ReturnMyVar(myvar)
End Sub

In the ReturnMyVar function I receive the variable as follows;

Public Function ReturnMyVar(myvar) As String
ReturnMyVar = myvar
End Function

This function has an argument and I think that is why I am getting the error message above.

Thank you,
Mabjro

 

[CajunCenturion]

Yes, the function ReturnMyVar is expecting a parameter (myvar) and that's the failure. You can include an arguement in the call itself, or make the parameter optional.

(((mytable.myfield) = ReturnMyVar(

MustHaveSomethingHere

)))

or

Public Function ReturnMyVar(

Optional

 myvar) As String

If you choose the optional route, then you need to handle the situation where the parameter is empty inside of the function.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cmmrfrds]

Make myvar a public variable and remove passing in function.

Public myvar as String
Public Function ReturnMyVar() As String
ReturnMyVar = myvar
End Function

 

[Mabjro]

Thank you for your replies. I will toy with this on Friday, as I am not near the computer this is on, at the moment. I will post back my results.

Mabjro

 

[Mabjro]

I tried to omit the argument (myvar)from the function and declaring it as a public variable per cmmrfrds and I get the following error;
"Wrong number of arguments or invalid property assignment"

I also tried to make the arguments optional per CajonCenturian. I do not get any errors with the query, but no records are returned.

I also tried to put an argument in the query itself via design view, but Jet inserts quotation marks around the argument and searches for the literal value "myvar".
I also tried to put an argument in the query itself via design view, but Jet inserts quotation marks around the argument and searches for the literal value "myvar".
Ex;
‘(((mytable.myfield) = ReturnMyVar("myvar&quot))’

I also tried to put an argument in the query itself via sql view, but Jet inserts []marks around the argument and I am not sure what it searches for, but no records are returned.

The final thing I tried was to set this up as the where condition of the docmd and depending on where I inserted the DoCmd object, I would get the error;
"You can't carry out this action at the present time"
Or I would get an error about the "Stack overload"

I forgot how to recreate the stack overload error and I did not write it down right away so I can't give the exact verbiage of the error.

Looks like I am going to have to work this one out on my own by taking a different approach all together.

Thanks for your help,
Mabjro

 

[vbajock]

If your getting stack overflows or Can't carry out etc, you got a code design problem.

I never liked the public variable approach. I think you should always set up two functions, one to set the value of the public variable and one to retrieve the value of the public variable.

For example I would dim:

Public myvar as whatever
in the same code modules as the function


I would add a function called

Public Function SetMyVar(byval somevar as something)
myvar=somevar
End function

in the same code module as

Public Function ReturnMyVar() As String
ReturnMyVar = myvar
End Function

In your got focus sub, I'd replace
myvar = Me.Description.Value
with
call Setmyvar(myvar)
This would give you much more efficient scoping.

Then use ReturnMyVar() in the query to get the value.

To troubleshoot it add a temporary stop:

Public Function ReturnMyVar() As String

ReturnMyVar = myvar
STOP

End Function
Run the query. It will stop at the function and you can use the immediate window to see what the computer thinks the value assigned to the public variable is.

 

[Mabjro]

Thank you VBAJock,

It will take me some time to experiment with your suggestions. I'm going to work on that and I will post back early next week. Have a great weekend everyone.

Mabjro

PS. Sorry about the duplicate paragraph above, I did not notice it when I previewed my post. Hopefully it did not make my question overly confusing.