Function call from Query

[shar]

I use a function call in Access 97 to pass variable data to the criteria field of a query. Up to now, the function was evaluating to a single string, such as "Completed" or "Active" and it worked fine. Now I need to have the function pass two value separated by an 'And' or an ‘Or’ operator to the query, i.e. "Completed Or Active".

I have tried many ways of placing quotation marks around the text, but non has worked. If I cut and paste "Completed" Or "Active" in the criteria field, the query returns data, but when the function evaluates to "Completed" Or "Active" or even ""Completed" Or "Active"" nothing gets returned.

Any comment/suggestion is appreciated.
Thanks.
Shar

 

[WaltLukeIII]

try concatenating your string before you pass it for example
you want to pass Completed and Active separated by an or

dim strpass

strpass = " completed " & " or " & " Active"

then pass strpass wherever it is you want to pass it to.


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[shar]

Thank you WaltLukeIII for the reply. I have tried various combination of concatnating it in the function, such as:
"""""Completed""" & " Or " & """Active"""""
or
"""Completed""" & " Or " & """Active"""

Neither works.

 

[WaltLukeIII]

I am talking about in your function once you get the two items
set them equal to a variable and concatenate that then send that with the quotes to the field


strpass = item1 & " or " & item2

send "'" & strpass & "'"
Or you might even try just passing
send strpass

in place of send use whatever code you are using to get the info to the query. I can't think of any other way to do this the other thing is make sure

you also might want to see what is being passed used msgbox command, to error check

msg box strpass right before you send it and right after you send it to check it.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[shar]

This function is in the criteria field of query:
Function ProjStat()
Dim it1, it2, strpass As String
it1 = "Completed"
it2 = "Active"
strpass = it1 & " or " & it2
ProjStat = strpass
End Function

I also tried:
ProjStat = "'" & strpass & "'"

No dice.
Thanks.

 

[MarkSweetland]

I think you're using a binary OR in place of the comparison OR you really want. If you pasted "Completed" OR "Active" in the criteria section of the query and then switched to the SQL view you will see the criteria broken down in the WHERE clause:

"WHERE status = "Completed" or status = "Active"

Since your function returns a single string I would think the WHERE clause would be something like:

"WHERE status = "'Completed' OR 'Active'"

Even with the additional quotes, the where clause would still not be correct.

Mark

 

[MichaelRed]

Shar,

Assuming you have done this EXACTLY, there is no need to have the function at all. The "function" ALWAYS returns the same thing! So, just place the fixed criteria in the condition. If there is a conditional, where the function is not called (so no criteria), just include that as an IIF "wrapper" for the two conditions, substituting the "*" if you don;t want the criteria.

If you were trying to get differnt criteria, depending on conditions,

It gets a little complicated, but you need to "clone" your single param function.

in the Criteria field use each of parameter functions, one for the first parameter ("It1&quot and the other for your second parameter ("It2&quot:

[ProjStat] AND [ProjStat2]

ProjStat just (always?) returns It1. ProjStat needs to be able to return It2, but to make the single criteria work, it alos needs to be able to returm "*" (for all/any).


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[shar]

MarkSweetland, you are correct, the Where clause is separated in SQL and as MichaelRed said I may have to have 2 separate function calls.

I had simplified the example to see if I can pass 2 variables with one call. In live data, the function picks between 8 different status, 7 of which are only one string (Active, Completed, Transferred, etc.), but the last one is "*" And Not "Deleted" (All status except deleted).

This function is used by SQL statements, which get created by the code during program execution. I was trying to avoid rewriting 20+ SQL statements.

Thanks for all input.