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

[gnawoncents]

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

Like nz([Forms]![FRMsurveyReports].[ClassUnique],"*") 

Code #2

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!

[jimatqsi]

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

[gnawoncents]

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:

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.

[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.

Private Sub cboObject1_AfterUpdate()
    Call SetRecordSources
end sub
 
Private Sub cboObject2_AfterUpdate()
    Call SetRecordSources
end sub
 
Private Sub cboObject3_AfterUpdate()
    Call SetRecordSources
end sub
 
Private Sub SetRecordSource()
dim strSQL as string
 
' Select records for combo box object cboObject2
strSQL = "Select X,Y,Z from tablename where 1 = 1 "
if not IsNull(cboObject1) then strsql = strSql & " AND ClassUnique Like '" & Forms]![FRMsurveyReports].[ClassUnique] & "*' "
' then add more ANDs for any other combo box objects that affect this filter before you set the new RecordSource
' You probably also have to add some grouping
 cboObject2.RecordSource = strSql
 dboObject2.requery
 
' Now do cboObject3, 4, 5 ... in similar fashion and so on
 
end sub

Jim

[gnawoncents]

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:

SELECT DISTINCT TBLSurveyResponses.SurveyID
FROM TBLSurveyResponses
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],"*")))
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.

[gnawoncents]

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.

Option Compare Database
 
Public strSQLreport As String
Public strCBchoice As String
Public strSQLwhere As String
 
 
Private Sub Form_Open(Cancel As Integer)
Call QuestionsModDTG_AfterUpdate ' set up the row sources
End Sub
 
 
Public Sub QuestionsModDTG_AfterUpdate()
 
' Clear criteria below this selection
Call BTNclearALL_Click
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
' Set up the record sources
strCBchoice = "ClassType"
Call SetRecordSource
 
strCBchoice = "Class"
Call SetRecordSource
 
strCBchoice = "ClassUnique"
Call SetRecordSource
 
strCBchoice = "SurveyDate"
Call SetRecordSource
 
strCBchoice = "ReplyRequested"
Call SetRecordSource
 
strCBchoice = "SurveyID"
Call SetRecordSource
 
Requery
Refresh
End Sub
 
Private Sub ClassType_AfterUpdate()
 
' Clear criteria below this selection
Me.Class = Null
Me.ClassUnique = Null
Me.SurveyDate = Null
Me.ReplyRequested = Null
Me.SurveyID = Null
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
' Set up the record sources
strCBchoice = "Class"
Call SetRecordSource
 
strCBchoice = "ClassUnique"
Call SetRecordSource
 
strCBchoice = "SurveyDate"
Call SetRecordSource
 
strCBchoice = "ReplyRequested"
Call SetRecordSource
 
strCBchoice = "SurveyID"
Call SetRecordSource
 
' Refresh the queries
Requery
Refresh
 
End Sub
 
Private Sub Class_AfterUpdate()
 
' Clear criteria below this selection
Me.ClassUnique = Null
Me.SurveyDate = Null
Me.ReplyRequested = Null
Me.SurveyID = Null
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
strCBchoice = "ClassUnique"
Call SetRecordSource
 
strCBchoice = "SurveyDate"
Call SetRecordSource
 
strCBchoice = "ReplyRequested"
Call SetRecordSource
 
strCBchoice = "SurveyID"
Call SetRecordSource
 
' Refresh the queries
Requery
Refresh
 
End Sub
 
Private Sub ClassUnique_AfterUpdate()
 
' Clear criteria below this selection
Me.SurveyDate = Null
Me.ReplyRequested = Null
Me.SurveyID = Null
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
' Set up the record sources
strCBchoice = "SurveyDate"
Call SetRecordSource
 
strCBchoice = "ReplyRequested"
Call SetRecordSource
 
strCBchoice = "SurveyID"
Call SetRecordSource
 
' Refresh the queries
Requery
Refresh
 
End Sub
 
Private Sub SurveyDate_AfterUpdate()
 
' Clear criteria below this selection
Me.ReplyRequested = Null
Me.SurveyID = Null
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
' Set up the record sources
strCBchoice = "ReplyRequested"
Call SetRecordSource
 
strCBchoice = "SurveyID"
Call SetRecordSource
 
' Refresh the queries
Requery
Refresh
 
End Sub
 
Private Sub ReplyRequested_AfterUpdate()
 
' Clear criteria below this selection
Me.SurveyID = Null
 
Call SetSourceWhere ' Set up the WHERE clause for RowSource
 
' Set up the record sources
strCBchoice = "SurveyID"
Call SetRecordSource
 
' Refresh the queries
Requery
Refresh
 
End Sub
 
Public Function SetRecordSource()
 
Dim strSQLsource As String
 
strSQLsource = "SELECT DISTINCT TBLSurveyResponses." & strCBchoice & " FROM TBLSurveyResponses " & _
               " WHERE TBLSurveyResponses." & strCBchoice & " Is Not Null AND" & strSQLwhere & _
               " ORDER BY TBLSurveyResponses." & strCBchoice & ";"
 
Me(strCBchoice).RowSource = strSQLsource
 
End Function
 
Public Function SetSourceWhere()
 
Dim strCBname As String
Dim ctlCB As Control
 
strSQLwhere = ""
'Loop through the controls, find those with selections and add to WHERE string
For Each ctlCB In Me.Controls
    If (ctlCB.ControlType = acComboBox) And IsNull(ctlCB) = False Then
       strCBname = ctlCB.Name
       strSQLwhere = strSQLwhere & " TBLSurveyResponses." & strCBname & " Like [Forms]![FRMsurveyReports]." & strCBname & " AND"
    End If
Next ctlCB
 
strSQLwhere = Left(strSQLwhere, Len(strSQLwhere) - 4) ' remove the trailing " AND"
 
End Function
 
 
Public Sub BTNclearALL_Click()
On Error GoTo Err_BTNclearALL_Click
 
'Loop through the controls, find those that are combo boxes and clear them
For Each ctl In Me.Controls
    If (ctl.ControlType = acComboBox) And ctl.Name <> "QuestionsModDTG" Then
       ctl.Value = Null
    End If
Next ctl
 
Requery
Refresh
 
Exit_BTNclearALL_Click:
        Exit Sub
 
Err_BTNclearALL_Click:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_BTNclearALL_Click
 
End Sub

Hopefully I'm not missing any of the pertinent code there...

[jimatqsi]

Very nice job. :) Glad you got it worked out.

Jim