Access help: VB

Toggle sidebar Toggle sidebar
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
What I am trying to do is filter a report using criteria set in a form based on the data input in the database. I've got my form, filters, etc set, but for some reason the attached code does not work.

Private Sub Set_Filter_Click()

Code: 
Dim strSQL As String, intCounter As Integer


  For intCounter = 1 To 4
      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
     
     strSQL = Left(strSQL, (Len(strSQL) - 5))

     Reports![date_report].Filter = strSQL
     Reports![date_report].FilterOn = True
   End If

End Sub

Any ideas as to why I keept getting an error on the strSQL = command?
 
Last edited:
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
minendo said:
What I am trying to do is filter a report using criteria set in a form based on the data input in the database. I've got my form, filters, etc set, but for some reason the attached code does not work.

Private Sub Set_Filter_Click()

Code: 
Dim strSQL As String, intCounter As Integer


  For intCounter = 1 To 4
      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
     
     strSQL = Left(strSQL, (Len(strSQL) - 5))

     Reports![date_report].Filter = strSQL
     Reports![date_report].FilterOn = True
   End If

End Sub

Any ideas as to why I keept getting an error on the strSQL = command?
Click to expand...

See if that works?
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KLin said:
See if that works?
Click to expand...

Nope. Still errors on:

Code: 
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
             & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" And ""
 
K

KIAman

Diamond Member
Mar 7, 2001
3,342
23
81
Why are you double quoting this?
Code: 
"" And ""

Remove double quotes. And if you MSUT double quote there (for some reason) then use what you have been using before and stay consistent: char(34)
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
KIAman said:
Why are you double quoting this?
Code: 
"" And ""

Remove double quotes. And if you MSUT double quote there (for some reason) then use what you have been using before and stay consistent: char(34)
Click to expand...

lol That's the problem. Just tested it out myself and it works when taking out those double quotes.
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KIAman said:
Why are you double quoting this?
Code: 
"" And ""

Remove double quotes. And if you MSUT double quote there (for some reason) then use what you have been using before and stay consistent: char(34)
Click to expand...

Removing that helped, but for some reason it does not accept the values from my combo boxes. Instead I get a popup asking to input parameter. Any ideas?
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
minendo said:
Removing that helped, but for some reason it does not accept the values from my combo boxes. Instead I get a popup asking to input parameter. Any ideas?
Click to expand...

When you open the properties for each field(design view, click field, F4 key) and click the other tab the name property needs to match "filter1", "filter2" and so on.
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KLin said:
When you open the properties for each field(design view, click field, F4 key) and click the other tab the name property needs to match "filter1", "filter2" and so on.
Click to expand...

All are set that way. It runs through the filter names properly. Do they need to have a control source set?
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
For example Filter 1 properties are:

Name: Filter1
Row Source Type: Table/Query
Row Source: SELECT Main.[Item #], Main.Date FROM Main;
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
Are you familiar with stepping through code in vba? You may want to see what strSQL is ending up looking like before setting it to the report's filter property.
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KLin said:
Are you familiar with stepping through code in vba? You may want to see what strSQL is ending up looking like before setting it to the report's filter property.
Click to expand...

Not at all. I haven't used VB since 10 years ago. I'm just trying to create a simple database with a filter option for the one of the reports.
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
Change Me("Filter" & intCounter).Tag to Me("Filter" & intCounter).Name and give it another try
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
How do I set the FilterOn expression in a report?

Edit: Nevermind figured filteron out. Still can't figure out why report does not filter.
 
Last edited:
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
PHP: 
Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer, first As Boolean
first = False

    If Not IsNull(Me.Filter1) Then
        first = True
        strSQL = "[ReportField1] = '" & Me.Filter1 & "'"
    End If
      
    If Not IsNull(Me.Filter2) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField2] = '" & Me.Filter2 & "'"
    End If
    
    If Not IsNull(Me.Filter3) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField3] = '" & Me.Filter3 & "'"
    End If
    
    If Not IsNull(Me.Filter4) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField4] = '" & Me.Filter4 & "'"
    End If
    
    If strSQL <> "" Then
        Reports![date_report].Filter = strSQL
        Reports![date_report].FilterOn = True
    End If
End Sub

Try that out. You need to change each ReportField to be whatever field you want to filter by in the report. (ie change ReportField1 to [Item #] in the code above and the same thing for the other 3).
 
Last edited:
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KLin said:
PHP: 
Dim strSQL As String, intCounter As Integer, first As Boolean
first = False

    If Not IsNull(Me.Filter1) Then
        first = True
        strSQL = "[ReportField1] = '" & Me.Filter1 & "'"
    End If
      
    If Not IsNull(Me.Filter2) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField2] = '" & Me.Filter2 & "'"
    End If
    
    If Not IsNull(Me.Filter3) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField3] = '" & Me.Filter3 & "'"
    End If
    
    If Not IsNull(Me.Filter4) Then
        If first Then
            strSQL = strSQL & " AND "
        Else
            first = True
        End If
        strSQL = strSQL & "[ReportField4] = '" & Me.Filter4 & "'"
    End If
    
    If strSQL <> "" Then
        Reports![date_report].Filter = strSQL
        Reports![date_report].FilterOn = True
    End If

Try that out. You need to change each ReportField to be whatever field you want to filter by in the report. (ie change ReportField1 to [Item #] in the code above and the same thing for the other 3).
Click to expand...

Gave me the following error: Data type mismatch in criteria expression.
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
minendo said:
Gave me the following error: Data type mismatch in criteria expression.
Click to expand...

Will this allow me to filter by just one filter or must all filters have data selected? I am needed to filter by any combo (sometimes just 1, other times multiple).
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
minendo said:
Will this allow me to filter by just one filter or must all filters have data selected? I am needed to filter by any combo (sometimes just 1, other times multiple).
Click to expand...

It will allow any combination of the combo boxes filled out. Are any of the combo boxes numeric values or dates?
 
minendo

minendo

Elite Member
Aug 31, 2001
35,560
22
81
KLin said:
It will allow any combination of the combo boxes filled out. Are any of the combo boxes numeric values or dates?
Click to expand...

The first combo box is a date. Two are text, and one is linked to a Yes/No field.

Would it be easier if I were to send you the file to review? Right now it is just filled with bogus data until I get it working properly.
 
KLin

KLin

Lifer
Feb 29, 2000
30,950
1,076
126
minendo said:
The first combo box is a date. Two are text, and one is linked to a Yes/No field.

Would it be easier if I were to send you the file to review? Right now it is just filled with bogus data until I get it working properly.
Click to expand...

That's fine. I'm at home sick and kinda bored. YHPM
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom