Query Criteria via Function

[bschulze]

I'm trying to provide the criteria for a query via a function, only because I could not get the like(isnull(iff statement to work properly. I have a form where the users select specific filter criteria for a report, I then read the forms data and pass the value to a query.

[CODE ]
Function FilterCompanyCode() As String
If IsNull(Forms!frmReports.cboEntity) Then
FilterCompanyCode = "Is Null Or """" Or Like ""*"""
Else
FilterCompanyCode = [Forms]![frmReports].[cboEntity]
End If
End Function
[/ CODE]

The FilterCompanyCode() is in the queries criteria. When the value in the combobox is not null the query returns the desired records. When the combobox is null the query does not return any records. When I step through the function and copy the variable directly to the query, it returns the proper recordset.

Anyone have any ideas why it wont work while running the function but does work when copied into the criteria of the query. Any help is greatly appreciated.

 

[mrmmickle1]

You have to use a % sign instead of an asterisk. Asterisks are only acceptable in the Access Application. When querying through code it's %.

 

[bschulze]

I gave it a shot but no luck. Instead of passing Is Null Or "" Or Like "*" I passed Is Null Or "" Or Like "%". The query returned zero records. Any other thoughts?

 

[mrmmickle1]

You may have an extra double quote in there....

Get the code to pass the value to FilterCompanyCode then use the immediate window to see what the value of the SQL is...


Ctrl + G (Then commit the below with enter after the FilterCompanyCode variable has a value)

?FilterCompanyCode

Is it what you expect?

If you plug that text into your query where the criteria is supposed to be does it perform as expected? Guessing not....

Here's an example of some code I've used in the past containing the % maybe it will help:

[COLOR=#0000ff][B]Public Const[/B][/COLOR] gcstrDoubleQuote [B][COLOR=#0000ff]As String[/COLOR][/B] = """"
[COLOR=#0000ff][B]Dim[/B][/COLOR] strSQL [COLOR=#0000ff][B]As String[/B][/COLOR]

            strSQL = "SELECT tblPayor.[Payor ID], tblPayor.[Payor Name]"
            strSQL = strSQL & vbLf & "FROM tblPayor"
            strSQL = strSQL & vbLf & "WHERE tblPayor.[Payor Name] NOT LIKE " & gcstrDoubleQuote & "%DNU%" & gcstrDoubleQuote & " AND"
            strSQL = strSQL & vbLf & "tblPayor.[Payor Name] NOT LIKE " & gcstrDoubleQuote & "%DO NOT USE%" & gcstrDoubleQuote & " AND"
            strSQL = strSQL & vbLf & "tblPayor.[Payor Name] NOT LIKE " & gcstrDoubleQuote & "%TEST INHOUSE%" & gcstrDoubleQuote & "  AND"
            strSQL = strSQL & vbLf & "tblPayor.[Payor Name] NOT LIKE " & gcstrDoubleQuote & "%USE HUM%" & gcstrDoubleQuote
            strSQL = strSQL & vbLf & "ORDER BY [Payor Name]"

 

[bschulze]

VBA line = FilterCompanyCode = "Is Null Or """" Or Like ""*"""
FilterCompanyCode Variable String = Is Null Or "" Or Like "*"

When I type the parameter into the query itself, it functions properly. When I pass the parameter via the function is returns zero records.

 

[mrmmickle1]

Are you actually looking for an asterisk or is that meant to be a wildcard symbol?

Can you give a small subset of non sensitive data and simplify the SQL to just a few fields for demonstration purposes?

What you expect to get.

What you actually get.

Not really sure of an easier way to identify your issue.

 

[bschulze]

The asterisk is a wildcard, symbolizing all records. Using a Like statement will not return the null values, hence the other Or statements.

If a user selects a filter then
use filter value
Else
show all records

 

[mrmmickle1]

The asterisk is applicable then:

I have gotten the results using this in the past:

   strSQL = "SELECT * FROM tblData"

[B][COLOR=#008000]'or maybe[/COLOR][/B]
        strSQL = "SELECT * FROM tblData"
        strSQL = strSQL & vbLf & "WHERE [ID] IS NOT NULL"

Not sure you need the like statement

I would build in a base string of SQL and then add to it based on the FORM Value. If Form Value = X then Sql = Sql & "One String" Else Sql = Sql & "Another String""

Here's another example:

[B][COLOR=#0000ff]If[/COLOR][/B] YOURFORMVALUE = "ALL" [COLOR=#0000ff][B]Then[/B][/COLOR]
                            strSQL = strSQL & vbLf & "FROM tblData"
[COLOR=#0000ff][B]                        Else[/B][/COLOR]
                            strSQL = strSQL & vbLf & "FROM tblData"
                            strSQL = strSQL & vbLf & "WHERE [Current Status] = " & gcstrDoubleQuote & strType & gcstrDoubleQuote
[COLOR=#0000ff][B]                        End If[/B][/COLOR]

 

[bschulze]

Not sure you are understanding the issue. I'm not dynamically creating a SQL string and using the docmd.runsql command. I already have a query, via query designer, created and I'm trying to pass a query parameter via a Function call.

Thanks for giving it a look. Anyone have any thoughts?

 

[mrmmickle1]

Here's my last attempt. Try something like this:

 tblData.Entity LIKE '*' Or tblData.Entity IS NULL Or tblData.Entity = ""

WHere tblData.Entity is your field