Prividing query criteria from function

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Here's the code I'm using


Public Function SelectedFunds() As String
Dim LowerVal As Variant
Dim UpperVal As Variant
Dim i As Integer
Dim str1 As String

'Lower Upper boundry
LowerVal = LBound(arrSelectedFunds)
UpperVal = UBound(arrSelectedFunds)


For i = LowerVal To UpperVal

If arrSelectedFunds(i) <> "" Then

If str1 <> "" Then

str1 = str1 & " Or '" & arrSelectedFunds(i) & "'"

Else

str1 = "'" & arrSelectedFunds(i) & "'"

End If

End If

Next

SelectedFunds = str1

End Function

The problem is that the string will *wrap* the string within, in double
quotes, so for example" str1 will look like: "'H61B' Or 'H61C' Or 'H61D'".
This will make the criteria for the query incorrect

Replace is not working. What else can I do??

Thanks a lot
 
Leo-

I gather you're using the returned string as part of a WHERE clause
somewhere else. If you construct a predicate with OR, then you must repeat
the field name, like this:

FundSymbol = 'ABCD' Or FundSymbol = 'DEFG' Or FundSymbol = 'HJKL'

A cleaner syntax uses the IN predicate:

FundSymbol IN ('ABCD', 'DEFG', 'HKJL')

So, your code could build a comma-delimited list without the Or operators.
The calling code would need to be modified to use IN and slap parens around
the returned string, as in:

strSQL = strSQL & "WHERE FundSymbol IN (" & _
SelectedFunds() & ")"

If that solution works for you, then change the line:

str1 = str1 & " Or '" & arrSelectedFunds(i) & "'"

To:

str1 = str1 & ", '" & arrSelectedFunds(i) & "'"


--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John, hello, and thank you very much for your reply!

Yes Indeed, I am using the function as a criteria for a query (this is a
physical query, not constructed through code). The IN clause was the first
thing I actually tried, but ran into the same problem. Because the function
is specified as a string it is enclosing my whole statement which I build in
the loop, in quotes:

So even though I am building the string like this: 'ABCD', 'EFGH', 'IJKL'

when I return it to the query it looks like this: "'ABCD', EFGH', 'IJKL'"
(notice the double quotes added because the data type for the variable is
String)

So my query sees it like this : IN("'ABCD','EFGH','IJKL'")

Again the starting and ending double quotes seems to be the problem.


Thanks for your help

Leo
 
Oh. You can't do that. When you call a function like this as part of a
predicate, the most you can substitute is a single literal value. You'll
run into the same problem with a parameter.

To solve your problem, you need to pass the field to be tested to the
function and have it return a True or False. Your query will look like:

SELECT SomeField
FROM MyTable
WHERE SelectedFunds([SomeField]) = True

And your function:

Public Function SelectedFunds(strField As String) As Integer
Dim LowerVal As Variant
Dim UpperVal As Variant
Dim i As Integer
'Lower Upper boundry
LowerVal = LBound(arrSelectedFunds)
UpperVal = UBound(arrSelectedFunds)
SelectedFunds = False
For i = LowerVal To UpperVal
If arrSelectedFunds(i) <> "" Then
If strField = arrSelectedFunds(i) Then
SelectedFunds = True
Exit Function
End If
End If
Next
End Function

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi John, I created something different but with the same logic as what you
suggest. It works (so does your suggestion)

I appreciate your help very much. As always you are able to find a solution
to the problem.


Cheers
Leo



John Viescas said:
Oh. You can't do that. When you call a function like this as part of a
predicate, the most you can substitute is a single literal value. You'll
run into the same problem with a parameter.

To solve your problem, you need to pass the field to be tested to the
function and have it return a True or False. Your query will look like:

SELECT SomeField
FROM MyTable
WHERE SelectedFunds([SomeField]) = True

And your function:

Public Function SelectedFunds(strField As String) As Integer
Dim LowerVal As Variant
Dim UpperVal As Variant
Dim i As Integer
'Lower Upper boundry
LowerVal = LBound(arrSelectedFunds)
UpperVal = UBound(arrSelectedFunds)
SelectedFunds = False
For i = LowerVal To UpperVal
If arrSelectedFunds(i) <> "" Then
If strField = arrSelectedFunds(i) Then
SelectedFunds = True
Exit Function
End If
End If
Next
End Function

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Leo said:
John, hello, and thank you very much for your reply!

Yes Indeed, I am using the function as a criteria for a query (this is a
physical query, not constructed through code). The IN clause was the first
thing I actually tried, but ran into the same problem. Because the
function
is specified as a string it is enclosing my whole statement which I build
in
the loop, in quotes:

So even though I am building the string like this: 'ABCD', 'EFGH', 'IJKL'

when I return it to the query it looks like this: "'ABCD', EFGH', 'IJKL'"
(notice the double quotes added because the data type for the variable is
String)

So my query sees it like this : IN("'ABCD','EFGH','IJKL'")

Again the starting and ending double quotes seems to be the problem.


Thanks for your help

Leo
Click to expand...
Click to expand...
 
Back
Top