Using Null in Listbox choice (A2k3, SP1)

Topic

New Reply

How about adding a check box (“allow null for agency”) to the form?

Reply | Quote

Replies

Thanks for the suggestion, but I have actually brought something like that up to the people I am doing this for and that didn’t fly with them. They are kinda “goofy” when it comes to things like this; i.e. extra keystrokes, “unnecessary” movement from using the keyboard to using the mouse, etc. So I said I’d try to make it part of the selection(s) from the listbox.
Thanks again.

gdr

Reply | Quote

(Edited by gdrezek on 16-Mar-05 10:48. strAuthSQL should have been strSQL throughout the example)

Hi All,
I trying to be able to include IsNull along with tangible values for a search string. Basically I wish to reproduce
“WHERE NumberPKID = ” & lngCaseNumberPKID
” AND (AgencyPKID IN ” & “(” & strAgencies & “)”
” OR AgencyPKID IS NULL)”

Following is an attempt at doing this:

strSQL = "SELECT DISTINCT ["there are ten fields selected] "
strSQL = strSQL & "FROM qryFormCases "
strSQL = strSQL & "WHERE CaseNumberPKID = " & lngIGICaseNumberPKID
	
If Not ctl2.ItemsSelected(0) = 0 Then
    For Each itm2 In ctl2.ItemsSelected
        If Len(ctl2.ItemData(itm2))  0 Then
            strAgencies = strAgencies & ctl2.ItemData(itm2) & ", "
        Else
            strSQL = strSQL & " OR AgencyPKID Is Null"
        End If
    Next itm2
    strAgencies = Left(strAgencies, Len(strAgencies) - 2)
    strSQL = strSQL & " AND AgencyPKID IN " & "(" & strAgencies & ")"
Else
    'do nothing
End If

Obviously I can’t seem to get this done correctly.
How is it possible to do this?
Thank you.

gdr

Reply | Quote

You can’t include Is Null in the list, you include it as part of the where condition.

Remove it from here:

strAuthSQL = strAuthSQL & " OR AgencyPKID Is Null"

And add it like this:

strAgencies = Left(strAgencies, Len(strAgencies) - 2)
strAuthSQL = strAuthSQL & " AND AgencyPKID IN " & "(" & strAgencies & ") OR AgencyPKID Is Null"

Reply | Quote

Hi Charlotte,
After reading your reply I looked at my original post and realized that I had typos in the strSQL string. strAuthSql should have been changed to strSQL. I edited that post.
I am trying to add IsNull as a choice to the strSQL along with any Agency they may wish to search by. The IN() may have 3 Agencies as entries. They also may have (what I am trying for) 2 Agencies (or 1 as the case may be) to search by as well as “AgencyPKID Is Null”. It’s that part I’m having fun with.
Since I made the correction edit, does that make my question more clear, rather than the “mud” I threw out in the original post?
Thank you.

gdr

Reply | Quote

Does this do what you want?

If Ctl2.ItemsSelected.Count > 0 Then
For Each itm2 In Ctl2.ItemsSelected
If Len(Ctl2.ItemData(itm2)) > 0 Then
strAgencies = strAgencies & Ctl2.ItemData(itm2) & “, ”
Else
strAuthSQL = “AgencyPKID Is Null”
End If
Next itm2
If Len(strAgencies) > 0 Then
strAgencies = “AgencyPKID IN (” & left(strAgencies, Len(strAgencies) – 2) & “)”
If Len(strAuthSQL) > 0 Then
strAgencies = strAgencies & ” OR ” & strAuthSQL
End If
ElseIf Len(strAuthSQL) > 0 Then
strAgencies = strAuthSQL
End If
If Len(strAgencies) > 0 Then
strSQL = strSQL & ” AND (” & strAgencies & “)”
End If
End If

Reply | Quote

Hi Hans,
re: my reply to Charlotte, I had made a copy/paste mistake in my original post and have edited it.
Will that “edit” your response? I got lost in your response because of my mis-direction in my original post.
Thank you.

gdr

Reply | Quote

The code as I posted it doesn’t have to be changed (including the strAuthSQL). Post back if it doesn’t work for you.

Reply | Quote

Hi Hans,
Thanks!
All I needed to do was to ensure the string values corresponded with my setup and
added — strAgencyNotNull = “AgencyPKID IN ” & “(” & strAgencies & “)”
to your work and here you go. The code I am using:

strSQL = "SELECT DISTINCT [again, 10 fields listed here] "
strSQL = strSQL & "FROM qryFormCases "
strSQL = strSQL & "WHERE CaseNumberPKID = " & lngCaseNumberPKID
	
If Not ctl2.ItemsSelected(0) = 0 Then
    For Each itm2 In ctl2.ItemsSelected
        If Len(ctl2.ItemData(itm2))  0 Then
            strAgencies = strAgencies & ctl2.ItemData(itm2) & ", "
        Else
            strAgencyNull = "AgencyPKID Is Null"
        End If
    Next itm2
    If Len(strAgencies) > 0 Then
        strAgencies = Left(strAgencies, Len(strAgencies) - 2)
        strAgencyNotNull = "AgencyPKID IN " & "(" & strAgencies & ")"
        If Len(strAgencyNull) > 0 Then
            strAgencyNotNull = strAgencyNotNull & " OR " & strAgencyNull
        End If
    ElseIf Len(strAgencyNull) > 0 Then
        strAgencyNotNull = strAgencyNull
    End If
    If Len(strAgencyNotNull) > 0 Then
        strSQL = strSQL & " AND (" & strAgencyNotNull & ")"
    End If
Else
    'do nothing
End If

So far it seems to work through all the various groupings I need.
Thanks again.

gdr

Reply | Quote