469,359 Members | 1,622 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,359 developers. It's quick & easy.

Query based on combo box needs to return all values if Null

gnawoncents
214 100+
I have a form (FRMsurveyReports) with several combo boxes (e.g. Class, ClassUnique, SurveyID, etc.) which use an SQL query based on a table (TBLSurveyResponses) for their record source. The available options filter based on any criteria selected in the combo boxes above them. If nothing is selected, all options should be visible.

For example, consider the following combo boxes below.

ClassUnique
SurveyID

Using code #1 below, if I leave the ClassUnique selection blank, SurveyID will only return records where the ClassUnique value is not null (because the wildcard character doesn't find null values). However, I can't seem to find a good variation on the code to include the nulls. Code #2 below is my latest attempt, but simply returns an error saying, “This expression is typed incorrectly, or is too complex to be evaluated.”

Code #1
Expand|Select|Wrap|Line Numbers
  1. Like nz([Forms]![FRMsurveyReports].[ClassUnique],"*") 
Code #2
Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![FRMsurveyReports].[ClassUnique] Is Null,([TBLSurveyResponses].[ClassUnique]) Like "*" Or ([TBLSurveyResponses].[ClassUnique]) Is Null,([TBLSurveyResponses].[ClassUnique]) Like [Forms]![FRMsurveyReports].[ClassUnique])
Any ideas? Thanks for your help!
Oct 6 '10 #1

✓ answered by jimatqsi

It's not clear to me where you are putting this fragment of code you listed.

I'm still for dynamically building your filters or RecordSources based on the entries. I generally do that by calling one common filtering subroutine, but you could build the code into each object's AfterUpdate event to selectively rebuild only the RecordSources that are affected by the object that just changed.

In the AfterUpdate event of all of your combo boxes, either a call to the filtering subroutine or the filtering code. In my example, I'll use a common subroutine.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboObject1_AfterUpdate()
  2.     Call SetRecordSources
  3. end sub
  4.  
  5. Private Sub cboObject2_AfterUpdate()
  6.     Call SetRecordSources
  7. end sub
  8.  
  9. Private Sub cboObject3_AfterUpdate()
  10.     Call SetRecordSources
  11. end sub
  12.  
  13. Private Sub SetRecordSource()
  14. dim strSQL as string
  15.  
  16. ' Select records for combo box object cboObject2
  17. strSQL = "Select X,Y,Z from tablename where 1 = 1 "
  18. if not IsNull(cboObject1) then strsql = strSql & " AND ClassUnique Like '" & Forms]![FRMsurveyReports].[ClassUnique] & "*' "
  19. ' then add more ANDs for any other combo box objects that affect this filter before you set the new RecordSource
  20. ' You probably also have to add some grouping
  21.  cboObject2.RecordSource = strSql
  22.  dboObject2.requery
  23.  
  24. ' Now do cboObject3, 4, 5 ... in similar fashion and so on
  25.  
  26. end sub
Jim

6 10020
jimatqsi
1,260 Expert 1GB
If a given combo box is blank just leave the corresponding field out of your where clause. There's no point in testing for everything, if everything is okay then just don't make the test.

It's not really clear what SurveyID is. Is that a saved query? If so, then this won't work, and I would suggest changing your code to dynamically build a string query to plug into the form's recordsource.

Or, if it is a saved query, you could take the tests out of that and dynamically build a filter for the form. Depends on the volume of data that might possibly be returned whether that might be okay or not.

Jim
Oct 7 '10 #2
gnawoncents
214 100+
Jim,

ClassUnique and SurveyID are two of the columns in my table, and while I use about ten to filter, I listed only those to simplify things.

My purpose in testing for everything is twofold:

1) Filter subsequent combo boxes based on selections above them

2) Filter records based on all selected combo boxes

There is one SurveyID for each record (this is a unique field). Conversely, each record can be assigned to one of many ClassUnique values, or none at all. I need to be able to have a user select ClassUnique value "A" from the combo box, and allow only the SurveyID selections in the corresponding combo box that have "A" as the ClassUnique. This isn't a problem. I run into a snag though when I leave the ClassUnique combo box selection empty. When I do not make a selection, the SurveyID field will only show me records that have something/anything in the ClassUnique field (it leaves out all the records with null values).

Example:

ClassUnique_______SurveyID

1234______________99999999
AAAA______________11112222
AAAA______________22223333
__________________44445555
BBBB______________66667777
__________________88889999
AAAA______________12121212
1234______________55555555

In the table above, with my working code (shown below), I get good results if I select AAAA, BBBB, or 1234. However, if I leave ClassUnique blank, I only have six SurveyID's to choose from (the ones without a ClassUnique do not appear).

Partially working code:
Expand|Select|Wrap|Line Numbers
  1. Like nz([Forms]![FRMsurveyReports].[ClassUnique],"*")
I understand why this code doesn't work (the "*" will not find Null values), I just haven't been able to find a good fix yet.
Oct 7 '10 #3
jimatqsi
1,260 Expert 1GB
It's not clear to me where you are putting this fragment of code you listed.

I'm still for dynamically building your filters or RecordSources based on the entries. I generally do that by calling one common filtering subroutine, but you could build the code into each object's AfterUpdate event to selectively rebuild only the RecordSources that are affected by the object that just changed.

In the AfterUpdate event of all of your combo boxes, either a call to the filtering subroutine or the filtering code. In my example, I'll use a common subroutine.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboObject1_AfterUpdate()
  2.     Call SetRecordSources
  3. end sub
  4.  
  5. Private Sub cboObject2_AfterUpdate()
  6.     Call SetRecordSources
  7. end sub
  8.  
  9. Private Sub cboObject3_AfterUpdate()
  10.     Call SetRecordSources
  11. end sub
  12.  
  13. Private Sub SetRecordSource()
  14. dim strSQL as string
  15.  
  16. ' Select records for combo box object cboObject2
  17. strSQL = "Select X,Y,Z from tablename where 1 = 1 "
  18. if not IsNull(cboObject1) then strsql = strSql & " AND ClassUnique Like '" & Forms]![FRMsurveyReports].[ClassUnique] & "*' "
  19. ' then add more ANDs for any other combo box objects that affect this filter before you set the new RecordSource
  20. ' You probably also have to add some grouping
  21.  cboObject2.RecordSource = strSql
  22.  dboObject2.requery
  23.  
  24. ' Now do cboObject3, 4, 5 ... in similar fashion and so on
  25.  
  26. end sub
Jim
Oct 8 '10 #4
gnawoncents
214 100+
Jim,

Thanks for the idea. I hadn't considered dynamically changing the record source -- I'm going to give it a shot. I apologize for not being clearer on where the code extract was coming from before. It was part of the Row Source coding in the property sheet section of the combo box. Here is the full code in SQL view:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT TBLSurveyResponses.SurveyID
  2. FROM TBLSurveyResponses
  3. WHERE (((TBLSurveyResponses.SurveyID) Is Not Null) AND ((TBLSurveyResponses.QuestionsModDTG) Like [Forms]![FRMsurveyReports].[QuestionsModDTG]) AND ((TBLSurveyResponses.ClassType) Like nz([Forms]![FRMsurveyReports].[ClassType],"*")) AND ((TBLSurveyResponses.Class) Like nz([Forms]![FRMsurveyReports].[Class],"*")) AND ((TBLSurveyResponses.ClassUnique) Like nz([Forms]![FRMsurveyReports].[ClassUnique],"*")) AND ((TBLSurveyResponses.SurveyDate) Like nz([Forms]![FRMsurveyReports].[SurveyDate],"*")) AND ((TBLSurveyResponses.ReplyRequested) Like nz([Forms]![FRMsurveyReports].[ReplyRequested],"*")))
  4. ORDER BY TBLSurveyResponses.SurveyID;
Again, thanks for your idea to handle it all in the after update event. I think that will work much better... I'll let you know.
Oct 8 '10 #5
gnawoncents
214 100+
All right, building on Jim's idea, I now have functioning code (see below). Hopefully this might help someone else with a similar problem. If anyone can see a better way to set this up, please advise. Thanks.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public strSQLreport As String
  4. Public strCBchoice As String
  5. Public strSQLwhere As String
  6.  
  7.  
  8. Private Sub Form_Open(Cancel As Integer)
  9. Call QuestionsModDTG_AfterUpdate ' set up the row sources
  10. End Sub
  11.  
  12.  
  13. Public Sub QuestionsModDTG_AfterUpdate()
  14.  
  15. ' Clear criteria below this selection
  16. Call BTNclearALL_Click
  17.  
  18. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  19.  
  20. ' Set up the record sources
  21. strCBchoice = "ClassType"
  22. Call SetRecordSource
  23.  
  24. strCBchoice = "Class"
  25. Call SetRecordSource
  26.  
  27. strCBchoice = "ClassUnique"
  28. Call SetRecordSource
  29.  
  30. strCBchoice = "SurveyDate"
  31. Call SetRecordSource
  32.  
  33. strCBchoice = "ReplyRequested"
  34. Call SetRecordSource
  35.  
  36. strCBchoice = "SurveyID"
  37. Call SetRecordSource
  38.  
  39. Requery
  40. Refresh
  41. End Sub
  42.  
  43. Private Sub ClassType_AfterUpdate()
  44.  
  45. ' Clear criteria below this selection
  46. Me.Class = Null
  47. Me.ClassUnique = Null
  48. Me.SurveyDate = Null
  49. Me.ReplyRequested = Null
  50. Me.SurveyID = Null
  51.  
  52. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  53.  
  54. ' Set up the record sources
  55. strCBchoice = "Class"
  56. Call SetRecordSource
  57.  
  58. strCBchoice = "ClassUnique"
  59. Call SetRecordSource
  60.  
  61. strCBchoice = "SurveyDate"
  62. Call SetRecordSource
  63.  
  64. strCBchoice = "ReplyRequested"
  65. Call SetRecordSource
  66.  
  67. strCBchoice = "SurveyID"
  68. Call SetRecordSource
  69.  
  70. ' Refresh the queries
  71. Requery
  72. Refresh
  73.  
  74. End Sub
  75.  
  76. Private Sub Class_AfterUpdate()
  77.  
  78. ' Clear criteria below this selection
  79. Me.ClassUnique = Null
  80. Me.SurveyDate = Null
  81. Me.ReplyRequested = Null
  82. Me.SurveyID = Null
  83.  
  84. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  85.  
  86. strCBchoice = "ClassUnique"
  87. Call SetRecordSource
  88.  
  89. strCBchoice = "SurveyDate"
  90. Call SetRecordSource
  91.  
  92. strCBchoice = "ReplyRequested"
  93. Call SetRecordSource
  94.  
  95. strCBchoice = "SurveyID"
  96. Call SetRecordSource
  97.  
  98. ' Refresh the queries
  99. Requery
  100. Refresh
  101.  
  102. End Sub
  103.  
  104. Private Sub ClassUnique_AfterUpdate()
  105.  
  106. ' Clear criteria below this selection
  107. Me.SurveyDate = Null
  108. Me.ReplyRequested = Null
  109. Me.SurveyID = Null
  110.  
  111. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  112.  
  113. ' Set up the record sources
  114. strCBchoice = "SurveyDate"
  115. Call SetRecordSource
  116.  
  117. strCBchoice = "ReplyRequested"
  118. Call SetRecordSource
  119.  
  120. strCBchoice = "SurveyID"
  121. Call SetRecordSource
  122.  
  123. ' Refresh the queries
  124. Requery
  125. Refresh
  126.  
  127. End Sub
  128.  
  129. Private Sub SurveyDate_AfterUpdate()
  130.  
  131. ' Clear criteria below this selection
  132. Me.ReplyRequested = Null
  133. Me.SurveyID = Null
  134.  
  135. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  136.  
  137. ' Set up the record sources
  138. strCBchoice = "ReplyRequested"
  139. Call SetRecordSource
  140.  
  141. strCBchoice = "SurveyID"
  142. Call SetRecordSource
  143.  
  144. ' Refresh the queries
  145. Requery
  146. Refresh
  147.  
  148. End Sub
  149.  
  150. Private Sub ReplyRequested_AfterUpdate()
  151.  
  152. ' Clear criteria below this selection
  153. Me.SurveyID = Null
  154.  
  155. Call SetSourceWhere ' Set up the WHERE clause for RowSource
  156.  
  157. ' Set up the record sources
  158. strCBchoice = "SurveyID"
  159. Call SetRecordSource
  160.  
  161. ' Refresh the queries
  162. Requery
  163. Refresh
  164.  
  165. End Sub
  166.  
  167. Public Function SetRecordSource()
  168.  
  169. Dim strSQLsource As String
  170.  
  171. strSQLsource = "SELECT DISTINCT TBLSurveyResponses." & strCBchoice & " FROM TBLSurveyResponses " & _
  172.                " WHERE TBLSurveyResponses." & strCBchoice & " Is Not Null AND" & strSQLwhere & _
  173.                " ORDER BY TBLSurveyResponses." & strCBchoice & ";"
  174.  
  175. Me(strCBchoice).RowSource = strSQLsource
  176.  
  177. End Function
  178.  
  179. Public Function SetSourceWhere()
  180.  
  181. Dim strCBname As String
  182. Dim ctlCB As Control
  183.  
  184. strSQLwhere = ""
  185. 'Loop through the controls, find those with selections and add to WHERE string
  186. For Each ctlCB In Me.Controls
  187.     If (ctlCB.ControlType = acComboBox) And IsNull(ctlCB) = False Then
  188.        strCBname = ctlCB.Name
  189.        strSQLwhere = strSQLwhere & " TBLSurveyResponses." & strCBname & " Like [Forms]![FRMsurveyReports]." & strCBname & " AND"
  190.     End If
  191. Next ctlCB
  192.  
  193. strSQLwhere = Left(strSQLwhere, Len(strSQLwhere) - 4) ' remove the trailing " AND"
  194.  
  195. End Function
  196.  
  197.  
  198. Public Sub BTNclearALL_Click()
  199. On Error GoTo Err_BTNclearALL_Click
  200.  
  201. 'Loop through the controls, find those that are combo boxes and clear them
  202. For Each ctl In Me.Controls
  203.     If (ctl.ControlType = acComboBox) And ctl.Name <> "QuestionsModDTG" Then
  204.        ctl.Value = Null
  205.     End If
  206. Next ctl
  207.  
  208. Requery
  209. Refresh
  210.  
  211. Exit_BTNclearALL_Click:
  212.         Exit Sub
  213.  
  214. Err_BTNclearALL_Click:
  215.         MsgBox "Error " & Err.Number & ": " & Err.Description
  216.         Resume Exit_BTNclearALL_Click
  217.  
  218. End Sub
Hopefully I'm not missing any of the pertinent code there...
Oct 9 '10 #6
jimatqsi
1,260 Expert 1GB
Very nice job. :) Glad you got it worked out.

Jim
Oct 11 '10 #7

Post your reply

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

Similar topics

8 posts views Thread by Steven Stewart | last post: by
1 post views Thread by Matthias Klöpper | last post: by
1 post views Thread by Jack Addington | last post: by
2 posts views Thread by amith.srinivas | last post: by
4 posts views Thread by barcaroller | last post: by
14 posts views Thread by =?Utf-8?B?QmVu?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.