Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    New Jersey, New Jersey, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

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

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Null in Listbox choice (A2k3, SP1)

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

    Remove it from here:

    <code>strAuthSQL = strAuthSQL & " OR AgencyPKID Is Null"</code>

    And add it like this:

    <code>strAgencies = Left(strAgencies, Len(strAgencies) - 2)
    strAuthSQL = strAuthSQL & " AND AgencyPKID IN " & "(" & strAgencies & ") OR AgencyPKID Is Null"</code>
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Null in Listbox choice (A2k3, SP1)

    <P ID="edit" class=small>(Edited by gdrezek on 16-Mar-05 10:48. strAuthSQL should have been strSQL throughout the example)</P>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:
    <pre>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
    </pre>

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

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

    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? <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

    Hi Hans,
    re: my reply to Charlotte, I had made a copy/paste mistake in my original post and have edited it. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Will that "edit" your response? I got lost in your response because of my mis-direction in my original post.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

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

  9. #9
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Null in Listbox choice (A2k3, SP1)

    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:
    <pre>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
    </pre>

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

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •