By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,450 Members | 980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,450 IT Pros & Developers. It's quick & easy.

Filter Query with Function

Brilstern
100+
P: 208
Ok I will try to simplify this as mush as possible.

I have a table:
Expand|Select|Wrap|Line Numbers
  1. tblTotalHosts
  2. Field        DataType
  3. ID           AutoNumber
  4. TotalHosts   Number
  5. Region       Text
  6. ReportMonth  Text
  7. 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.
Expand|Select|Wrap|Line Numbers
  1.     strRegion = Me.cbxRegion.Value
  2.     strMonth = Me.cbxMonth.Value
  3.     strYear = Me.cbxYear.Value
  4.  
  5.     If strRegion = "All Regions" Then
  6.  
  7.         strRegion = "*"
  8.  
  9.     End If
  10.  
  11.     If strMonth = "All Months" Then
  12.  
  13.         strMonth = "*"
  14.  
  15.     End If
  16.  
  17.     If strYear = "All Years" Then
  18.  
  19.         strYear = "*"
  20.  
  21.     End If
  22.  
  23.     strExportRegion = strRegion
  24.     strExportMonth = strMonth
  25.     strExportYear = strYear
  26.  
  27.     DoCmd.Close
I have used this function populate the query filter on demand.
Expand|Select|Wrap|Line Numbers
  1. Function getStrRegion()
  2.  
  3.     getStrRegion = strExportRegion
  4.  
  5. End Function
  6.  
  7. Function getStrMonth()
  8.  
  9.     getStrMonth = strExportMonth
  10.  
  11. End Function
  12.  
  13. Function getStrYear()
  14.  
  15.     If Not (strExportYear = "*") Then
  16.  
  17.     getStrYear = CDbl(strExportYear)
  18.  
  19.     End If
  20.  
  21. End Function
Then I put that function as the where filter.
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblTotalHosts.TotalHosts) AS SumOfTotalHosts
  2. FROM tblTotalHosts
  3. WHERE (((tblTotalHosts.Region) Like getStrRegion()) AND ((tblTotalHosts.ReportMonth) Like getStrMonth()) AND ((tblTotalHosts.ReportYear) Like getStrYear()));
  4.  
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!
Mar 6 '15 #1

✓ answered by twinnyfo

Stevan,

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub TestSQL()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT ...."
  4.     Debug.Print strSQL
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. "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!

Share this Question
Share on Google+
2 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
Stevan,

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub TestSQL()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT ...."
  4.     Debug.Print strSQL
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. "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!
Mar 6 '15 #2

Brilstern
100+
P: 208
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:

Expand|Select|Wrap|Line Numbers
  1. Function getStrRegion()
  2.  
  3.     getStrRegion = strExportRegion
  4.  
  5. End Function
  6.  
  7. Function getStrMonth()
  8.  
  9.     getStrMonth = strExportMonth
  10.  
  11. End Function
  12.  
  13. Function getStrYear()
  14.  
  15.     If Not (strExportYear = "*") Then
  16.  
  17.     getStrYear = CDbl(strExportYear)
  18.  
  19.     Else
  20.  
  21.     getStrYear = strExportYear
  22.  
  23.     End If
  24.  
  25. 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!
Mar 6 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.