filter listbox with combobox

[silentwolf]

Hi to all,

after a day searching trying and testing I am a bit frustrated of not getting anywhere with this problem and hope someone can give me some help please!
In a pageview I have a listbox and several textboxes and two comboboxes to filter the listbox.
I get the filter up and running with the textboxes but have problems with getting the combobox to filter the listbox.
The listbox rowsource is a query... and a field in this query is call Obj_Active... wich is a bolean so a yes no field.
I created a combobox with All, Yes, No fields own fields and like to filter the listbox with either all Object so no filter .. yes for only active Objects and no for not Active Objects..
found alot on the net but not something which gets me up and running with this.

Would be greatly appreciated if someone could get me on the right track with this.

Many thanks

Albert

 

[silentwolf]

This is what I have done so far but as I mentioned unfortunatelly it is not working

Private Sub cboAktiv_Change()

    Dim strSQL As String
    
    Select Case Me!cboAktiv
        Case "Ja"
        strSQL = "SELECT tblObjekt.Obj_id, tblObjekt.Obj_Name, tblObjekt.Obj_Adresse, tblObjekt.Obj_Ort, tblObjekt.Obj_Plz, tblObjekt.Obj_Land, tblObjekt.Obj_Aktiv, tblObjekt.Obj_FixAuftrag " & vbCrLf & _
            "FROM tblObjekt " & vbCrLf & _
            "WHERE (((tblObjekt.Obj_Aktiv)=Yes));"

        Case "Nein"
        
        strSQL = Me!lstObjekte.RowSource = "SELECT * FROM qryObjekte WHERE Obj_Aktiv  = Nein"

    End Select
    
    'Inhalt des Listenfeldes aktualisieren
    Me!lstObjekte.Requery
End Sub

On the first Select Case statement I tried a different approach but both are not working..

Thanks for help!"

 

[Micron]

First, I would not use vbCrLf in a sql statement (not needed, and maybe even detrimental) and I never use line continuation characters, but that's my preference. I find it's too easy to add/miss a space. I've copied such code from MS web, pasted into Notepad then into a procedure and it failed until I used my own method. Obviously you will choose your own way, but here's how I do it:

strSQL = "SELECT tblObjekt.Obj_id, tblObjekt.Obj_Name, tblObjekt.Obj_Adresse, tblObjekt.Obj_Ort, tblObjekt.Obj_Plz, "
strSQL = strSQL & "tblObjekt.Obj_Land, tblObjekt.Obj_Aktiv, tblObjekt.Obj_FixAuftrag FROM tblObjekt WHERE "
strSQL = strSQL & "(((tblObjekt.Obj_Aktiv)=True));"

I decided long ago that the last character in my line would always be a space.

I'm assuming your field type is Yes/No, but you say Boolean also. I think your main problem is that you think Yes/No fields translate as such, but they do not. The underlying values for yes/no are -1 and 0; not to be confused with Boolean, which is 1 and 0. You can use -1 or 0 (or 1,0 depending on data type) in your sql, or the constants True or False, but not "yes" or "no". You should research yes/no fields, starting here:
http://allenbrowne.com/noyesno.html
Some sites get right down to the binary values for true/false and say that the best method is to say <> 0 rather than = -1.

 

[silentwolf]

Hi Micron,
thank you for your reply and your explaination!! Ok I will look in to it!

Thanks you and merry christmas

 

[Micron]

PS: not sure if you noticed I used the value of True in my code attempt, hoping it would work for you as I wrote it.
You're welcome and Merry Christmas to you too!

 

[silentwolf]

Hi Micron,
sorry did not see your reply! well yes I did notice how you wrote it Itried but still have some problems with it... but not really at this problem at the moment so I will look into it when I find a bit more time again...
Thanks for your wishes too!