Handle Empty Variable

[Evagrius]

Hi all,

I have several multi-select listboxes that are used to filter a report. I would like to allow the user not to select anything from the listbox, but that produces an error when the variable is blank? How can I fix this w/out having to resort to a dozen SELECT CASE? So if the variable strWhere is blank I get the error. . . .Thank you!

    DoCmd.OpenReport "RPT", acViewReport, , "[Budget Fiscal] IN(" & strWhere & ")" & _
  "And [Building Number] IN(" & strWhereB & ")" & "And [budget] IN(" & StrWhereC & ")" & _
  "And [Organization Code] IN(" & strWhereD & ")"

 

[vbaInet]

Evagrius, simply check if it's empty in an IF ...ELSE ...END IF block and if it's empty don't add the WHERE argument.

If Len(Nz(strWhere, "")) = 0 then

else

end if

 

[Evagrius]

Hi vbaInet - I just didn't want to have to write an if block to check every variable - but if you think that is the best way then I'll stick with it. This means that I will have to have serveral Docmd.openreport, one to activate for each potenial scenario, right?


Is there no way to include everything in the IN operator?

Thanks!

 

[vbaInet]

Or you can use an IIF() function for each one of them. Keeps it in one line.

Better to write more code if it helps to optimise your db.

 

[Simon_MT]

You could try creating your Criteria first that tests for values:

Private Function SalesReports_Criteria()

    SalesReports_Criteria = Null
    With CodeContextObject
        If Not IsNull(.[Artist]) = True Then
                SalesReports_Criteria = "[Artist] = '" & .[Artist] & "'"
        End If

        If Not IsNull(SalesReports_Criteria) = True And Not IsNull(.[Client]) = True Then
            SalesReports_Criteria = SalesReports_Criteria & " and [Client Sale Client] = '" & .[Client] & "'"
        ElseIf IsNull(SalesReports_Criteria) = True And Not IsNull(.[Client]) = True Then
            SalesReports_Criteria = "[Client Sale Client] = '" & .[Client] & "'"
        End If

        If Not IsNull(SalesReports_Criteria) = True And Not IsNull(.[Employees]) = True Then
            SalesReports_Criteria = SalesReports_Criteria & " and [Employee] = '" & .[Employees] & "'"
        ElseIf IsNull(SalesReports_Criteria) = True And Not IsNull(.[Employees]) = True Then
            SalesReports_Criteria = "[Employee] = '" & .[Employees] & "'"
        End If
    End With
    
End Function

Simon

 

[boblarson]

Hi vbaInet - I just didn't want to have to write an if block to check every variable - but if you think that is the best way then I'll stick with it. This means that I will have to have serveral Docmd.openreport, one to activate for each potenial scenario, right?


Is there no way to include everything in the IN operator?

Thanks!

I build a SINGLE where clause by doing an IF then Else for each part. Similar to this:

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
        If Not IsNull(Me.cboSelectName) Then
            stWhere = "[NamesID]=" & Me.cboSelectName & " And "
            blnTrim = True
        End If
 
        If Not IsNull(Me.cboSelectCity) Then
            stWhere = stWhere & "[CityID]=" & Me.cboSelectCity & " And "
            blnTrim = True
        End If
 
        If IsNull(Me.txtFrom) And Me.txtFrom = "" Then
            If Not IsNull(Me.txtTo) And Me.txtTo <> "" Then
                stWhere = stWhere & "[TravelDate]  <=" & Me.txtTo & "#"
                blnTrim = False
            End If
        Else
            If IsNull(Me.txtTo) And Me.txtTo = "" Then
                If Not IsNull(Me.txtFrom) And Me.txtFrom <> "" Then
                    stWhere = stWhere & "[TravelDate]>=" & Me.txtFrom
                    blnTrim = False
                End If
            Else
                If (Not IsNull(Me.txtFrom) And Me.txtFrom <> "") And (Not IsNull(Me.txtTo) Or Me.txtTo <> "") Then
                stWhere = stWhere & "[TravelDate] Between #" & Me.txtFrom & "# And #" & Me.txtTo & "#"
                blnTrim = False
                End If
            End If
        End If
 
        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If

    stDocName = "rptTipsLog"

    DoCmd.OpenReport stDocName, acPreview, , stWhere

 

[gemma-the-husky]

for stuff like this, i would tend to store the textbox/combobox value in a global variable, and then have a function that uses the variable to test whether to include the item in the query results.

then your query just needs a sortof

where myfunc(arg1, arg2, arg3 etc etc) = true

you probably need a minor bit of tweaking to allow for nulls in the argument list

 

[Evagrius]

Hi - I would like to thank you all for your expert help. I employed your suggestions today and the code is perfect. This board is amazing. Thank you all!