VBA code for filters 4

[RevJohn0584]

I keep getting a run time error of "run time error '13': Type mismatch" on a microsft office (internet help) provided solution to multiple filters for a report. (

http://support.microsoft.com/kb/208529).

I have followed the steps, changed the criteria to match my required searches, and the VBA debugger takes me to this particular line of code:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 5
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![rptNonStdPkData].Filter = strSQL
Reports![rptNonStdPkData].FilterOn = True
End If
End Sub

Highlighted area in debugger is emphasized here with ---> <--- showing the area of concern. I do not know what else to say for your assistance, except this is the problem I am facing. Everything else I have done on this works well.

Please help!

John

 

[HarleyQuinn]

Not sure why the And "" is there, try:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
             & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34)
      End If

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[RevJohn0584]

That stopped the run time error, however, I know see this:

Syntax error in string in query expression '([] = "NSE1)'

NSE1 is a customer location.

What else can I provide to help you untangle my mess?

Thank you for your time, HarleyQuinn!

 

[HarleyQuinn]

Is the tag property you're trying to include in the [] set to a value (as it looks like it's a blank at the minute)?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[TheAceMan1]

How are ya RevJohn0584 . . .

Perhaps the following will do:

[blue]   Dim Cri As String, x As Integer, Nam As String
   
   For x = 1 To 5
      Nam = "Filter" & x
      
      If Trim(Me(Nam) & "") <> "" Then
         If Cri <> "" Then
            Cri = Cri & " AND " & Me(Nam).tag & "='" & Me(Nam) & "'"
         Else
            Cri = Me(Nam).tag & "='" & Me(Nam) & "'"
         End If
      End If
   Next

   If Cri <> "" Then
      Reports![rptNonStdPkData].Filter = Cri
      Reports![rptNonStdPkData].FilterOn = True
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RevJohn0584]

Looks like we are making some headway...however, I am seeing this now;

when I open the form, the follwing occurs:

'Select Distinct [ShipDate] FROM qryNonStdPkData BY [ShipDate];' may not be a valid setting for the RowSourceType poperty, or there was a compile error in the function

additionally:

When I click the "Set Filter" command button, the following occurs:

Syntax error (missing operator) in query expression '(='07/10/09')'.

Thank you guys for your continued support. This new issue is after I changed the original VBA to match AceMan1's suggestion. I am sure there is something I am missing.....

 

[HarleyQuinn]

'Select Distinct [ShipDate] FROM qryNonStdPkData BY [ShipDate];' is missing the word ORDER in front of BY.

As for the second problem, I'm going to say again about the .Tag property of the control. Does it in fact contain a value or is it blank (asstill appears to be the case...)?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[RevJohn0584]

HarleyQuinn,

Not familiar with the .tag control.....so I am not sure what to ensure is filled.

 

[RevJohn0584]

I did some poking around and found the tag spot in properties on the combo boxes. I have them fille din now with the appropriate lnames, and still receive the error mentioned above.

Thank you again for working this through with me folks!

 

[lameid]

The type mismatch error indicates the value you are using is not the same data type as the field being used.

For clarity can you post your code, and indicate the line that is generating the error? It is possible that one thing is fixed and antother problem has cropped up. Possibly not all your fields are text and your value should not be quote delimited in that case?

 

[RevJohn0584]

one field is date...00/00/00 format (mm/dd/yy). how would I fix that?

Man I love being a newbie.

 

[lameid]

Text needs double quotes or single quotes (the last code post uses single quotes) as delimeters.

Dates require what Americans call the Pound Sign... I think brits call it the hash (#).

Finally, numbers do not use delimeters at all.

If you are willing to make the assumption that text fields will never have things that are a vaild date or valid number...

   Dim Cri As String, x As Integer, Nam As String
   Dim strDelimeter as string

   For x = 1 To 5
      Nam = "Filter" & x
      
      If Trim(Me(Nam) & "") <> "" Then
         IF Isnumeric(Me(Nam)) then
              strDelimeter = ""

              IF IsDate(Me(Nam)) then
                   strDelimeter = "#"
              Else
                    strDelimeter = "'"

              End If
         End If
         If Cri <> "" Then
            Cri = Cri & " AND " & Me(Nam).tag & "=" & strDelimeter & Me(Nam) & strDelimeter
         Else
            Cri = Me(Nam).tag & "=" & strDelimeter & Me(Nam) & strDelimeter
         End If
      End If
   Next

   If Cri <> "" Then
      Reports![rptNonStdPkData].Filter = Cri
      Reports![rptNonStdPkData].FilterOn = True
   End If

If you can't make that assumption, then you'd have to code in building each component of the criteria to handle the literals. Or at least that is the way I'd go.

 

[RevJohn0584]

This is the code I am using - I went back to the original codeset.

I am getting a syntax error in the part with the ----> <----- highlighted.

Private Sub cmdApplyFilter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 5
--> If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
""<-----
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptNonStdPkData4].Filter = strSQL
Reports![rptNonStdPkData4].FilterOn = True
End If
End Sub

Sorry I fell of the face of the earth...had an emergency come up. Thanks again all!

 

[lameid]

Please RE-READ my posts.

I see why that will fail as a fileter(two double quotes at the end), in addition to the data type problem.

 

[RevJohn0584]

Thank you lameid...I was not notified of your previous posts, so I did not know they existed. I will read, comply and update!

 

[RevJohn0584]

getting the following now:

Syntax error (missing operator)in query expression '(PartNumb=100408K AND ShipDest='08068' AND ShipDate='8/3/2009 AND Printed?='-1' AND Supervisor = 'J.Skeen')

Thank you for hanging in there!

 

[RevJohn0584]

Just for relevance and clzarification, I am posting the entire codeset for this database and all facets therein:

Option Compare Database

Private Sub cmdApplyFilter_Click()
Dim Cri As String, x As Integer, Nam As String
Dim strDelimeter As String

For x = 1 To 5
Nam = "Filter" & x

If Trim(Me(Nam) & "") <> "" Then
If IsNumeric(Me(Nam)) Then
strDelimeter = ""

If IsDate(Me(Nam)) Then
strDelimeter = "#"
Else
strDelimeter = "'"

End If
End If
If Cri <> "" Then
Cri = Cri & " AND " & Me(Nam).Tag & "=" & strDelimeter & Me(Nam) & strDelimeter
Else
Cri = Me(Nam).Tag & "=" & strDelimeter & Me(Nam) & strDelimeter
End If
End If
Next

If Cri <> "" Then
Reports![rptNonStdPkData4].Filter = Cri
Reports![rptNonStdPkData4].FilterOn = True
End If
End Sub


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

Private Sub cmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptNonStdPkData4", acViewPreview, , Me.Filter
End If

End Sub
Private Sub cmdClearFilter_Click()
Me.Filter = ""
End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
DoCmd.Close acReport, "rptNonStdPkData4" 'Close the Non Standard Pack Data report
DoCmd.Restore 'Restore the window size
End Sub



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


Private Sub Form_Open(Cancel As Integer)
Me.Filter = ""
DoCmd.OpenReport "rptNonStdPkData4", A_PREVIEW 'Open Non Standard Pack Data Report
DoCmd.Maximize 'Maximize the report window 'Maximize the report
End Sub



Private Sub cmdPrintRpt_Click()
On Error GoTo Err_cmdPrintRpt_Click

Dim stDocName As String

stDocName = "mcoOpnRpt1"
DoCmd.RunMacro stDocName

Exit_cmdPrintRpt_Click:
Exit Sub

Err_cmdPrintRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRpt_Click

End Sub
Private Sub cmdPrntRpt_Click()
On Error GoTo Err_cmdPrntRpt_Click

Dim stDocName As String

stDocName = "rptNonStdPkData4"
DoCmd.OpenReport stDocName, acNormal

Exit_cmdPrntRpt_Click:
Exit Sub

Err_cmdPrntRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrntRpt_Click

End Sub


I hope this helps in your attempts to untangle my newbiness.

Thanks again.

 

[lameid]

This seems suspect to me...

Private Sub cmdOpenReport_Click()
    If Me.Filter = "" Then
        MsgBox "Apply a filter to the form first."
    Else
        DoCmd.OpenReport "rptNonStdPkData4", acViewPreview, , Me.Filter
    End If
    
End Sub

The Me.Filter is passing the same filter on the form to the report. That may be the source of your problem.

Some other thoughts if this isn't the problem...

Does the query behind the report run?

If so, Design the report and ensure the filter is blank and the filter on is no and save the report and rerun.

 

[RevJohn0584]

The Me.Filter is passing the same filter on the form to the report. That may be the source of your problem."

Forgive me my ignorance, but I do not know how to rectify this if it is indeed the issue. This line of code is to open the report, and then it is kept behind the filter form while the criteria is filled in and then the "apply filter" command button is clicked, updating the report and bringing it to view.


I believe we are close to the root of this, and should be able to rectify it soon. Many thanks for your continued determination to help this new guy though his VB woes!

 

[lameid]

Don't use a filter...

 DoCmd.OpenReport "rptNonStdPkData4", acViewPreview

Although, if you are keeping the report hidden, it would be better to open the report with the appropriate filter which you are currently building on an on click event separately.

For now, just try it without the filter.