Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA code for filters 4

Status
Not open for further replies.
RevJohn0584

RevJohn0584

Technical User
Jul 9, 2009
32
US
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. ( 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
 
Sort by date Sort by votes
Syntax error (missing operator)in query expression '(PartNumb=100408K AND ShipDest='08068' AND ShipDate='8/3/2009 AND Printed?='-1' AND Supervisor = 'J.Skeen')
Click to expand...

This also looks odd...If this is the exactly the error that is showing up, your code isn't putting this string together correctly.

The PartNumb criteria should have single quotes around the value (PartNumb='100408K') as it looks like a text data type field.

The ShipDate criteria should have # signs around the value since it's a date data type (as lameid stated earlier). Somehow it's even missing the end single quote around that value.

In looking at your code though, I'm not even sure how this would happen. Maybe step through your code and watch how it's check the value of the Cri variable each time it goes through the loop.

Also, as to the cmdOpenReport_Click() sub, I wouldn't think you'd need to set a filter upon opening the report since you are already setting it at the end of the cmdApplyFilter_Click() sub here:

Code: 
 If Cri <> "" Then
      Reports![rptNonStdPkData4].Filter = Cri
      Reports![rptNonStdPkData4].FilterOn = True
   End If
 
Upvote 0 Downvote
I think Joel missed my most recent posts... The only thing that he said explicitly that I didn't is the filter is wrong. My thought is that the filter is wrong on the form and not coming from the filter building code.
 
Upvote 0 Downvote
Actually, in taking a closer look, I think the problem might be here:
Code: 
IF Isnumeric(Me(Nam)) then
   strDelimeter = ""

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

The way I read this, if Me(Nam) is not numeric, the strDelimiter variable will never have a value.

Try changing it to this:

Code: 
IF Isnumeric(Me(Nam)) then
   strDelimeter = ""
ElseIf IsDate(Me(Nam)) then
   strDelimeter = "#"
Else
   strDelimeter = "'"
End If

or you could use a select case statement

Code: 
Select Case True
   Case IsNumeric(Me(Nam))
      strDelimeter = ""
   Case IsDate(Me{Nam))
      strDelimeter = "#"
   Case Else
      strDelimeter = "'"
End Select
 
Upvote 0 Downvote
joeflorando,

by changing this it did move the ' from one spot to another in the error, but I still get the exact same error.

The sad and scary part is, i copied this code cirectly from microsoft's access help site. Sheeesh.

Folks, what can I provide to make this easier for you to help me? Can I zip and email this database to someone so they can see it in action?

Please advise!
 
Upvote 0 Downvote
Joel is right I left out an Else out of my code... That still doesn't explain the filter you had. Kill the me.filter and add the Else and report back.

Code: 
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 = ""
         [red][b]ELSE[/b][/red]
              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
 
Upvote 0 Downvote
RevJohn, we cross posted...

Joel's changes look like they are equivalent.

Add this just before the last If where I am assuming the error must be occuring...

Code: 
debug.print cri

Click the button, hit ctrl+G and at the bottom copy the text in the bottom pane (immediate window) and post it here.
 
Upvote 0 Downvote
syntax error (missing operator) in query expression '(PartNumb = '100408K' AND ShipDest = 08068 AND ShipDate = #8/3/2009# AND Printed? = -1 AND Supervisor = 'J.Skeen')'.

The ' is moving around =)
 
Upvote 0 Downvote
Ah at last the field name Printed? is a problem since the code is not putting square brackets around fields...

Code: 
         If Cri <> "" Then
            Cri = Cri & " AND [red][[/red]" & Me(Nam).Tag & "[red]][/red]=" & strDelimeter & Me(Nam) & strDelimeter
         Else
            Cri = [red]"[" & [/red] Me(Nam).Tag & "[red]][/red]=" & strDelimeter & Me(Nam) & strDelimeter
         End If
 
Upvote 0 Downvote
A couple things...
Is ShipDest actually a numeric field? Usually, the leading zeros get dropped if so, so I'm kind of guessing that it's not and should have quotes around the value.

Also, I think the bigger problem might be the ? in the Printed? field. Is there actually a ? in the name of the field? The question mark is reserved for single character wildcard so I think this might be confusing Access.
 
Upvote 0 Downvote
Hmmm... missed the ShipDest, good call.


Code: 
If IsNumeric(Me(Nam))[red] AND Val(Me(Nam)) = Me(Nam) [/red] Then
 
Upvote 0 Downvote
Click the button, hit ctrl+G and at the bottom copy the text in the bottom pane (immediate window) and post it here"

PartNumb='100408K' AND ShipDest=08068 AND ShipDate=#8/3/2009# AND Printed?=-1 AND Supervisor='J. Skeen'

I have removed the "Printed?" and made it "Printed". Still getting this error. Thanks for sticking with me on this guys!
 
Upvote 0 Downvote
joeflando - the ShipDest can have any combination of alpha and numeric characters in it. i.e. AP04A or SSPO-1, etc. etc. Where would I put quotes around that value in the code?
 
Upvote 0 Downvote
Modify your if statement like lameid pointed out and that should catch the ShipDest so that it has the proper quotes around it.

So you're still showing an error that has Printed? with the question mark? If so, that field name is still somewhere. Perhaps in the .tag property of one of those fields on the form...?
 
Upvote 0 Downvote
I have updated the code as lameid pointed out. That and removing the ? in the tag has opened a new doors - however =)

Now the is working, but not as it should. When I sort by, say, ShipDest, it doesn't show the destination I know is there (i.e. P025A), or only a few of many (i.e. DTK3A), or when I sort by PN it will show some of the parts, but not the part number I am typing in....

So, I am now beyond clueless. I apologize for being a tax on ya'll, and I thank you again for sticking with me on this!

Please let me know what I can do to help you help me!
 
Upvote 0 Downvote
Also:

When I type in a pure number into ShipDest, i get a "type mismtach" error....i.e. 03116 which is a ship code for a customer. My ship codes are as follows:

17419, AP04A, P025A, SSPO-1, etc. Varied numeric and aplha charcters in each, or all numeric, or in some cases, all aplha character.
 
Upvote 0 Downvote
Post the SQL of the query behind your report and the current incarnation of the Code with all the changes.

When you say you have a problem sorting by the field do you mean filter? Sort is ordering records, filtering is limiting the results.
 
Upvote 0 Downvote
sorry for the misnomer - filtering it is.

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)) And Val(Me(Nam)) = Me(Nam) Then
strDelimeter = ""
Else
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
Debug.Print Cri
If Cri <> "" Then
Reports![rptNonStdPkData4].Filter = Cri
Reports![rptNonStdPkData4].FilterOn = True
End If
End Sub

SQL from immediate screen:

PartNumb='100419H' AND ShipDest=03116 AND Printed=0

 
Upvote 0 Downvote
ShipDest still isn't getting the proper quotes. Looks like the val function isn't working as expected...

Maybe try

Code: 
If IsNumeric(Me(Nam)) And Left(Me(Nam),1) <> "0" Then

Sorry. Not very elegant but I can't think of anything else at the moment. Maybe someone else has a better idea.
 
Upvote 0 Downvote
You are simply going to have to trap ShipDest as an exception because of values that really are numbers like 17419. Also I did not expect the implicit conversion of the text to a number, force a string comparison instead. See the changes in Red.

Code: 
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)) And [red]cstr([/red]Val(Me(Nam))[red])[/red] = Me(Nam) [red]And Me(Nam) <> "ShipDest" [/red] Then
              strDelimeter = ""
         Else
              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
Debug.Print Cri
   If Cri <> "" Then
      Reports![rptNonStdPkData4].Filter = Cri
      Reports![rptNonStdPkData4].FilterOn = True
   End If
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Omarahmed22
  • Locked
  • Question
VBA Formating
Replies
1
Views
457
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
510
Andrzejek
Andrzejek
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
2K
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
329
Moss100
Moss100
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
344
TheAceMan1
TheAceMan1

Part and Inventory Search

Sponsor

Back
Top