Filter Query with Function

[Brilstern]

Ok I will try to simplify this as mush as possible.

I have a table:

[B]tblTotalHosts[/B]
[I][U]Field        DataType[/U][/I]
ID           AutoNumber
TotalHosts   Number
Region       Text
ReportMonth  Text
Report Year  Number

I would like to query this table based on values in fields Region, ReportMonth, and Report Year.

This will be use to generate a report with data from multiple tables. (this is just one example)

So I have a form that has three combo box's and the user can select either all for each field or narrow down by it respective data type.

This form then passes the selected values to public strings to be used in the Where filter on my query.

    strRegion = Me.cbxRegion.Value
    strMonth = Me.cbxMonth.Value
    strYear = Me.cbxYear.Value
    
    If strRegion = "All Regions" Then
    
        strRegion = "*"
    
    End If
    
    If strMonth = "All Months" Then
    
        strMonth = "*"
    
    End If
    
    If strYear = "All Years" Then
    
        strYear = "*"

    End If
    
    strExportRegion = strRegion
    strExportMonth = strMonth
    strExportYear = strYear

    DoCmd.Close

I have used this function populate the query filter on demand.

Function getStrRegion()

    getStrRegion = strExportRegion
    
End Function

Function getStrMonth()

    getStrMonth = strExportMonth
    
End Function

Function getStrYear()

    If Not (strExportYear = "*") Then

    getStrYear = CDbl(strExportYear)
    
    End If
    
End Function

Then I put that function as the where filter.

SELECT Sum(tblTotalHosts.TotalHosts) AS SumOfTotalHosts
FROM tblTotalHosts
WHERE (((tblTotalHosts.Region) Like getStrRegion()) AND ((tblTotalHosts.ReportMonth) Like getStrMonth()) AND ((tblTotalHosts.ReportYear) Like getStrYear()));

For some reason my query value ends up being blank when I know that I am selecting data that is valid. Let me know what I can do to expand upon my question. Thanks!

[twinnyfo]

Stevan,

First, I would add a speck of code to a public module and evaluate the actual SELECT Statement:

Public Sub TestSQL()
    Dim strSQL As String
    strSQL = "SELECT ...."
    Debug.Print strSQL
End Sub

This will allow you to evaluate the SQL string as it is actually used by the DB engine before it gets used. Then, you can check all your values.

However, I do have some possible recommendations :

"WHERE (((tblTotalHosts.Region) Like '" & getStrRegion() & "') AND ((tblTotalHosts.ReportMonth) Like '" & getStrMonth()& "') AND ((tblTotalHosts.ReportYear) Like '" & getStrYear()& "'));"

Notice the addition of the single quotes around the "Like" criteria.

Hope this hepps!

[Brilstern]

twinnyfo you're a genius!

We both looked over the actual issue but going back and debugging the SQL helped me see it.

In the string conversion functions I forgot something:

Function getStrRegion()
 
    getStrRegion = strExportRegion
 
End Function
 
Function getStrMonth()
 
    getStrMonth = strExportMonth
 
End Function
 
Function getStrYear()
 
    If Not (strExportYear = "*") Then
 
    getStrYear = CDbl(strExportYear)

    [B]Else[/B]

    [B]getStrYear = strExportYear[/B]

    End If
 
End Function

I wasn't passing the string to the function unless I selected a year. Well when I was testing it most of the time I was using "All Years." Thanks for the help!