Filter

[niki]

Filter using VB

Hello! I use Access 2002

I found this code on the microsoft support site. I tried to use it with my own tables. So I changed the names of the reports and the tables they were refering to in a clean manner. This code is the code of an action button which takes the values from three comboboxes and filters the data shown on a report.

When I try to use these filters, I get the correct values on the drop down list but when I push the go button, Access tells me I'm missing an operator, so I believe there's a syntax error somewhere.
Apparently, this code stores the value chosen in my filters. Could someone explain to me quickly these code lines???

Thx
nico

here's an explanation but its still not clear to me...
http://support.microsoft.com/default.aspx?scid=kb;en-us;208529

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 3
       If Me("Filter" & intCounter) <> "" Then
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
       End If
     Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = left(strSQL, (Len(strSQL) - 3))
        'Set the Filter property
        Reports![rptCustomers].Filter = strSQL
        Reports![rptCustomers].FilterOn = True
     Else
        Reports![rptCustomers].FilterOn = False
     End If

End Sub

 

[AncientOne]

This simply takes the contents of your Combo boxes and stitches them together with "ANDs" and spaces to make an SQL statement. Then it lops the last "AND" off and applies the SQL as a filter to the report.

There's a lot more to the process than this: the chances are that you have made a mistake in transposing the code. The quickest answer is to post all the code so it can be checked out.

 

[niki]

Hey thanks for your answer ancient!
Here's the full code. I thought it would be too long but if it can help solve the problem... Here you go:

Option Compare Database
Option Explicit

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 3
       If Me("Filter" & intCounter) <> "" Then
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
       End If
     Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Reports![rptCustomers].Filter = strSQL
        Reports![rptCustomers].FilterOn = True
     Else
        Reports![rptCustomers].FilterOn = False
     End If

End Sub


Private Sub Command29_Click()

    Dim intCouter As Integer

    For intCouter = 1 To 3
        Me("Filter" & intCouter) = ""
    Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptCustomers"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptCustomers", A_PREVIEW
DoCmd.Maximize
End Sub

c ya and thx again

 

[AncientOne]

Before the End Sub statement of Command 28 insert;

MsgBox strSQL

Run the code and post EXACTLY what appears in the Message box.

 

[niki]

Hey ancient!
well I pasted the code that shows the sql statement and when I run my filters the message box gives me the values entered in my three filters....

here's the dbs

 

[niki]

Hey ancient!
If you downloaded my file, there were stupid mistakes in the properties of my filters....
I changed them
It seems like I can now filter for a client which has two opportunities (filter1 on client 1 gives two results) and reduce this to one opportunity on the report by choosing one of the two results in my filter 2.

Yeeeeeepeeeeeee!!!

But now how about my reliability????

I can't get it to work along with my two filters... I dunno why...
I am attaching the partially fixed/working file again.

thx to anyone who can help me!!
nico

 

[AncientOne]

Yes I found some typos in your code etc. You fiabilite is wrong, because it goes into the sql as a string when it's a number. Unless you are actually using this in a mathematical operation, you can change its type to string without any ill-effect. Try that...

 

[niki]

THX!
Okay I understand verbally what you are saying but when it comes down to editing the code I don't know what to do can you give me a hint please? which code line should I change to get my "fiabilité" recognised as a numbered value?

thx

nico

 

[AncientOne]

Nico

This code isn't the right way to proceed. Your first combo should be client. This should filter the second combo box (Contact), otherwise you'll be able to select a contact that isn't associated with the client and this will always give you an empty recordset.

The same goes for reliability. If you select a reliability that isn't associated with a client and contact, you'll get an empty report again.

You need to have cascading combo boxes to overcome this problem.

If you're proceeding along your present lines, go into table design and change fiabilite from a number type to a text type. That should overcome your immediate problem. You don't need to change any code.

 

[niki]

Yeah!!!!
I'd like to do those cascading comboboxes!!! But I am not good enough so I'll stick to my first goal which is much easier!!

Again, I got this example code from microsoft support and that's the way they were solving a similar problem!!

how about changing what I want????
Don't mind if my users get an empty recordset though,means they are stupid and that they don't pay attention to what they are doing...
Now I'd like to offer the possibility to the users to show only offers where the reliability is greater than 50% that should be easy???

 

[AncientOne]

You can't do that to your poor users!

I will make you a set of cascading combos tonight and post them tomorrow morning.

The MS support thing was for a slightly different purpose...

 

[AncientOne]

Ok, here's a more refined version of your combos. You don't have to enter anything in the combos - if so, you'll get all records. If you choose a client, you'll get the contacts filtered. If you want reliability, you choose between >50,<50 or All (leave combo blank. ) You must clear the combos between selections, they are locked after update.

 

[niki]

No one can stop you ancient one!
thanks for the help I truly appreciate it. Since I am really new to all this I hadn't seen I had an invalid type for my third combo using numbers....

Thanks again
cheers