Re: Prividing query criteria from function

From: John Viescas (JohnV_at_nomail.please)
Date: 01/18/05 

Date: Mon, 17 Jan 2005 23:14:40 -0600

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" <none@none.com> wrote in message 
news:OsZEN8L$EHA.1260@TK2MSFTNGP12.phx.gbl...
> 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
>
>
>
>
> "John Viescas" <JohnV@nomail.please> wrote in message
> news:emKhBkL$EHA.3988@TK2MSFTNGP11.phx.gbl...
>> 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)
>> "Leo" <none@none.com> wrote in message
>> news:%23aCdI5K$EHA.2584@TK2MSFTNGP09.phx.gbl...
>> > 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
>> >
>> >
>> >
>>
>>
>
>

Relevant Pages

  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)
  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • Re: Selecting certain items for a report
    ... I created the query called tmpSelectProducts ... > highlights the Dim qdf as DAO.QueryDef line. ... > Dim StrWhere As String ... > End Sub ...
    (microsoft.public.access.forms)
  • Re: Dynamic Query, Using ListBox and VBA
    ... SELECT DISTINCT tblCompanies.strCompanyCountries FROM tblCompanies UNION ... Using a Microsoft Access Listbox to pass criteria to a query ... Dim MyDB As DAO.Database ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • RE: Recordset looping (and debug looping!)
    ... create a new query using the following SQL: ... I named it "Sndx" with a datatype of String. ... Dim rst As DAO.Recordset, strNames As String ... ' good name - add soundex code and save record ...
    (microsoft.public.access.formscoding)
Loading