Filter FormTable to show only "Yes" from Yes/No BOX

[iskov]

Hello there!

I am running into Trouble with my current database.

I have a table with "Services"

I then made a FormTable that lists all of them.

Each "Service" has a field called "Erledigt" (German)

This is a Yes/no Field.

I have made a button - This button is supposed to be clicked and then only Show the ones that are ticked "Yes" and then back to the original when clicked once again, is this possible??

Kind Regards
Anders

[twinnyfo]

Try something like this in the module for your form:

Option Compare Database
Option Explicit
 
Private fFilter As Boolean
 
Private Sub cmdFilter_Click()
    Me.Filter = "Erledigt = " & _
        IIf(fFilter, "True", "False")
    Me.FilterOn = True
    fFilter = not fFilter
End Sub

Tschüss!

[iskov]

Vielen Dank, dass war sehr erfolgreich!!!!
;-)

[iskov]

@Twinny

If I want to add a msgbox to say "filtered for 'erledigt'." and "Filtered for None-'erledigt'" where do I put the msgbox's??

Kind Regards

[twinnyfo]

I would recommend changing the Caption on the Command Button:

Me.cmdFilter.Caption = IIf(fFilter, "Un-Filter", "Filter")

between lines 6 and 7....

[iskov]

Thank you once again, I like the idea alot!

[twinnyfo]

Glad I could be of assistance!

[iskov]

I am going to pump this post as I am running into a minor Problem.

Basically the Report button works perfectly, however my form ALSO has a search field:

Private Sub Befehl104_Click()
Dim strsearch As String
Dim strText As String
Dim filterstr As String
 
If (Len(Txtsearch.Value) > 0) Then
    strText = Me.Txtsearch.Value
    strsearch = "SELECT * from ServiceAuftragTabelleQ where ((Unternehmen like ""*" & strText & "*"") or (NiederlassungName like ""*" & strText & "*"") or (AuftragAxapta like ""*" & strText & "*"") or (AuftragKunde like ""*" & strText & "*"") or (Typ like ""*" & strText & "*"") or (Art like ""*" & strText & "*"") or (DatumAngelegt like ""*" & strText & "*"") or (DatumEinsatzGeplant like ""*" & strText & "*"") or (AnsprechspartnerKunde like ""*" & strText & "*"") or (AnsprechspartnerITEC like ""*" & strText & "*"") or (ServicemitarbeiterITEC like ""*" & strText & "*""))"
    Me.RecordSource = strsearch
Else
    strsearch = "SELECT * from ServiceAuftragTabelleQ"
    Me.RecordSource = strsearch
End If
 
Me.Filter = "Erledigt = " & _
   IIf(fFilter, "False or True", "False")
Me.FilterOn = True
End Sub

The searchfield has the above showed button connected.

This allows me to search for words in all of my different informations for every Service contract in my table.

However if I for instance search for customer "Danish Crown" the Report button will still only Show all of the "Erledigt" or both if I have the filter activated or not:

Private Sub Befehl101_Click()
Me.Befehl101.Caption = IIf(fFilter, "Alle anzeigen", "Nur offene anzeigen")
     Me.Filter = "Erledigt = " & _
         IIf(fFilter, "False", "False or True")
     Me.FilterOn = True
     fFilter = Not fFilter
 
End Sub
 

That is the filter button, and this is what the Report button Looks like at the Moment:

Private Sub Befehl109_Click()
Dim strWhere As String
 If Me.Dirty Then Me.Dirty = False
 If Me.FilterOn Then strWhere = Me.Filter
 DoCmd.OpenReport "ServiceAufträgeR", acViewPreview, , strWhere
End Sub
 

If anything is unclear, please feel free to ask!

Anders Iskov

[twinnyfo]

Anders,

First, one question per thread, please, as this allows other users to search for answers more easily.

Second, be sure when you post code on the forum that you use Code Tags (highlight your desired text and then click the "[CODE/]" button.

Third, I'm not exactly sure what your question is in the above. Some of it may have to do with the mixture of English and German in the code. Also, looking back at my code in Post #2, I see in line 8, it should read:

IIf(fFilter, True, False)

Notice the lack of quotes around the True and False Values. This may be setting off your filter issues.

[iskov]

I am sorry, I have changed the codes so they are alittle easier to look at.

Should I make a new thread?

My Problem is basically:

The Report-filter works perfectly fine along with the button that filters for "erledigt" or "non-erledigt" HOWEVER when I use my search field (txtsearch) with the button to search with, the Report still only Shows ALL of the erledigt or ALL of the non-erledigt. I would preferably be able to look up a customer (that has several Service contracts) and ONLY print those out.

I much appriciate the help I have gotten so far, and I do not want to break any Forum rules, I just thought I would get an easier answer when I posted in the same thread.

Anders Iskov

[twinnyfo]

I think what you are asking could stay in this thread, as we seem to be still talking about filtering on "Erledigt"--both for the Form and the Report.

Mods, please advise if we should start a new thread.

First, concerning the code, if Erledigt is a Yes/No field, then this will not work:

Me.Filter = "Erledigt = " & _
    IIf(fFilter, "False or True", "False")

It should more properly be:

Me.Filter = IIf(fFilter, "Erledigt = True Or Erledigt = False", _
    "Erledigt = False")

This will properly set the filter to your desired results.

Concerning the report, keep in mind that you must also filter your Report according to the same criteria as your Form. Even though the Form is based on a Table and you have filtered the data in the Table for the Form, all the data in the Table still exists, so when the report gathers its data, it is looking at all the data. One way of opening the report this way is to perform a similar function when you call the Report:

DoCmd.OpenReport "ReportName", acViewPreview, , _
    IIf(fFilter, "Erledigt = True Or Erledigt = False", _
        "Erledigt = False")

I'm not sure if this is bringing you closer to solution, as I am still just a bit unsure of your question, as your final statement

I would preferably be able to look up a customer (that has several Service contracts) and ONLY print those out.

seems to imply a bit more....

[iskov]

This does not solve my Problem is at will still only Show ALL of the "erledigt" or ALL of the "non-erledigt"

I have at the Moment in my database two different customers:

Danish Crown Konzern

And

Tönnies Konzern

in a form I Show Information such as CustomerName, Subsidiary, data etcetc, in my searchfield (txtsearch) that is connected with the button:

Private Sub Befehl104_Click()
Dim strsearch As String
Dim strText As String
Dim filterstr As String
 
If (Len(Txtsearch.Value) > 0) Then
    strText = Me.Txtsearch.Value
    strsearch = "SELECT * from ServiceAuftragTabelleQ where ((Unternehmen like ""*" & strText & "*"") or (NiederlassungName like ""*" & strText & "*"") or (AuftragAxapta like ""*" & strText & "*"") or (AuftragKunde like ""*" & strText & "*"") or (Typ like ""*" & strText & "*"") or (Art like ""*" & strText & "*"") or (DatumAngelegt like ""*" & strText & "*"") or (DatumEinsatzGeplant like ""*" & strText & "*"") or (AnsprechspartnerKunde like ""*" & strText & "*"") or (AnsprechspartnerITEC like ""*" & strText & "*"") or (ServicemitarbeiterITEC like ""*" & strText & "*""))"
    Me.RecordSource = strsearch
Else
    strsearch = "SELECT * from ServiceAuftragTabelleQ"
    Me.RecordSource = strsearch
End If
 
Me.Filter = "Erledigt = " & _
   IIf(fFilter, "False or True", "False")
Me.FilterOn = True
End Sub

this allows me two fx only Show the customer-contracts from Tönnies, when I wanna Report just those, the Report will still Show ALL of them.

EDIT: Unternehmen is CustomerName and Niederlassung is Subsidiary (think that would be the best Translation)

[twinnyfo]

Still sticking with this thread for now.....

First let me suggest a minor tweak to somewhat shorten and clean up the code:

Private Sub Befehl104_Click()
    Dim strSearch As String
    Dim strText As String
    Dim filterstr As String
 
    If (Len(txtSearch.Value) > 0) Then
        strText = " Like '*" & Me.txtSearch.Value & "*'"
        strSearch = "SELECT * FROM ServiceAuftragTabelleQ " & _
            "WHERE ((Unternehmen" & strText & _
                ") OR (NiederlassungName" & strText & _
                ") OR (AuftragAxapta" & strText & _
                ") OR (AuftragKunde" & strText & _
                ") OR (Typ" & strText & _
                ") OR (Art" & strText & _
                ") OR (DatumAngelegt" & strText & _
                ") OR (DatumEinsatzGeplant" & strText & _
                ") OR (AnsprechspartnerKunde" & strText & _
                ") OR (AnsprechspartnerITEC" & strText & _
                ") OR (ServicemitarbeiterITEC" & strText & "))"
        strSearch = strSearch & _
            IIf(fFilter, "", " AND Erledigt = False")
        Me.RecordSource = strSearch
    Else
        strSearch = "SELECT * from ServiceAuftragTabelleQ"
        Me.RecordSource = strSearch
    End If
End Sub

Note how strText includes the "Like" keyword, and the usage of single quotes. Additionally, I included the Filter string directly into the SQL Statement.

My big question is, Where is this code located? If this is on the Form, it will not affect the report at all. This is a different story altogether.

You would need to set the record source of the report to the same as that of the Form that is displayed. Or, create a standalone query that takes into account all of the search criteria.

You have several options, all of which would require some rework.

However, I would recommend, at this point, beginning a new thread with a specific question concerning filtering the report based on multiple criteria, as this topic has gone beyond filtering a table based on a Yes/No field.

Hope this hepps!