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

 

[lameid]

So PN is like ShipDest and needs to be treated as text if a number is passed to it. I think you can figure this one out... Below is the line you will need to change.

 If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" Then

 

[RevJohn0584]

lameid,

I am sorry, but I am not sure I have this figured out. The line you posted is the one that we changed earlier on to deal with the ShipDest issue. DO I add an OR and then outline "PN" to be affected as well?

If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" OR "PN" Then

Is that it?

 

[lameid]

No you need And. If it is equal to either field, you don't want to treat it as numeric. Also you use the Boolean Operators on things that are boolean. Either functions that return a boolean or a logical test. A logical test has something to test, a comparison operator (in this case <>) and the value to be tested. Each logical test much have all three elements, you can't use the boolean operators on the value list.

The QBE can give you this impression because you can use them that way on the criteria line. However if you switch to SQL view and look at the where clause then you will see the SQL works the same way too.

 

[RevJohn0584]

If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" And "PN" Then

gives a run time error.

this line was highlighted in the debug.

I am obviously not getting it.

 

[RevJohn0584]

If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" And Me(Nam) And Me(Nam).Tag <> "PN" Then

I think I got it!!!!!!!!

 

[RevJohn0584]

OK, it works when it runs alone, but when I combine both ShipDest and PN as a filter criteria set, I get a type mismatch error 13....i.e.:

PN: 301154 = returns fine or
ShipDest: DTK3A = returns fine

PN: 301154 and ShipDes: DTK3A returns "type mismatch error 13"

What did I miss or do wrong?

 

[lameid]

That was the right change.

What does the filter string look like (the immediate window thing)? The code looks like it sould return a valid criteria string...

PN = "301154" AND ShipDest = "DTK3A"

 

[joelflorendo]

Not sure if this is what's causing the error but I think you need to remove the part in red:

If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" [COLOR=red]And Me(Nam)[/color] And Me(Nam).Tag <> "PN" Then

 

[RevJohn0584]

you. guys. rock.

It is working very well. That last issue joe addressed was spot on.

I do believe we have victory, guys.

Thank you sooooo very much for all of your assistance.

 

[RevJohn0584]

Last update:

All is working well and this tool is doing exactly what we needed. Many thanks again to all who helped!!!!!!