By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Filtering with access

P: 21
Hi Every One

I am New to the discoution, My problem is:
I am trying to filter a report by using cambo box and Comand bottons, Combo Box to set the filter value bottens for close,clear and apply filter to report.always getting type mismatch runtime error 13 when i debug error it hi light this line

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

Which it is part of this code

Private Sub Set_Filter_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![rptTransactions].Filter = strSQL
Reports![rptTransactions].FilterOn = True
End If

End Sub

Can Some 1 help me fix this problem?!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!..
Aug 15 '07 #1
Share this Question
Share on Google+
11 Replies


kestrel
Expert 100+
P: 1,071
Hi Every One

I am New to the discoution, My problem is:
I am trying to filter a report by using cambo box and Comand bottons, Combo Box to set the filter value bottens for close,clear and apply filter to report.always getting type mismatch runtime error 13 when i debug error it hi light this line

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

Which it is part of this code

Private Sub Set_Filter_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![rptTransactions].Filter = strSQL
Reports![rptTransactions].FilterOn = True
End If

End Sub

Can Some 1 help me fix this problem?!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!..
Hey Iyhamid, Welcome to theScripts. im going to move this to the SQL forum. Have a nice day.
Aug 15 '07 #2

Purple
Expert 100+
P: 404
Hi All,

I think the thread may be happier in the access forum.

Regards Purple
Aug 15 '07 #3

Scott Price
Expert 100+
P: 1,384
Good morning lyhamid,

First of all, what version of what program are you using?

Second, what is the output you would like to see from this strSQL statement?

i.e. [Filter1] = "Filter1" or something like that?

Regards,
Scott
Aug 15 '07 #4

P: 21
Hello
I am Using Access Of Office 2003, Filter1 is A merchant ID contain of 8 numbers..
Thanks
Aug 15 '07 #5

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. Private Sub Set_Filter_Click()
  2. Dim strSQL As String, intCounter As Integer
  3.  
  4. ' Build SQL String.
  5. For intCounter = 1 To 3
  6. If Me("Filter" & intCounter) <> "" Then
  7.  
  8. strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
  9. & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
  10. And ""
  11. End If
  12. Next
  13.  
  14. If strSQL <> "" Then
  15. ' Strip Last " And ".
  16. strSQL = Left(strSQL, (Len(strSQL) - 3))
  17.  
  18. ' Set the Filter property.
  19. Reports![rptTransactions].Filter = strSQL
  20. Reports![rptTransactions].FilterOn = True
  21. End If
  22.  
  23. End Sub
You haven't explained all the information we really need yet, but I'm going to make some assumptions.

Assumption #1: "Filter" in your code is referring to a control on your form. In this case you need to refer to it in this way: Me!Filter.

To build your strSQL, you want it to look more like this:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "[" & Me!Filter & intCounter & "]" = & Chr$(34) & Me!Filter & intCounter & Chr$(34) & " And "
You have it set to add " And " at the end of your filter string, and then are trying to take those characters away later if a condition is not met. The problem is, your code won't take them all away! Your code will only take the furthest right three characters, which will leave the space and capital A intact. Maybe that's what you intend, otherwise you'll need to increase your character count to 5 (remember VBA counts a space within a string as a character) in your Left() statement.

Your Next statement needs to look more like this: Next intCounter

Make these few changes, then please let me know if it works!

Regards,
Scott
Aug 15 '07 #6

P: 21
I will Try that when i go to work 2morrow.. i got this code from this site..
http://support.microsoft.com/?kbid=208529..

Thanks Man i will get back to you asap..
Aug 15 '07 #7

P: 21
Hello Scott:

Sorry I didn't Fully explain setuation..(Set Filter) is a command Botten to run the previouse code..
Combo Boxes named Filter1, Filter2, Filter3 and Taged as Merchant Id,Date and transaction ID. The program call it by Tages when u loop the counter.. Also the form open the rpttransactions report.. The form that filtered by filter1, filter2 and filter3
When I put ur code it came with syntax error

strSQL = "[" & Me!Filter & intCounter & "]" = & Chr$(34) & Me!Filter & intCounter & Chr$(34) & " And "

Thanks
Iehab
Aug 16 '07 #8

P: 21
Hello Scott I got a diffrent error after I change it alittle bite, But now I am getting the Filter1 value and the others value but it will say value no valid even if i get it from the report..I change it to:

strSQL = "[" & Me("Filter" & intCounter).Tag & "]" & Chr$(34) & Me("Filter" & intCounter) & Chr$(34) & " And "

Thanks
Iehab
Aug 16 '07 #9

Scott Price
Expert 100+
P: 1,384
Hello Scott I got a diffrent error after I change it alittle bite, But now I am getting the Filter1 value and the others value but it will say value no valid even if i get it from the report..I change it to:

strSQL = "[" & Me("Filter" & intCounter).Tag & "]" & Chr$(34) & Me("Filter" & intCounter) & Chr$(34) & " And "

Thanks
Iehab
Good morning!

Did you download the sample Access 2000 report on this link? Access 2000 Sample Reports

I downloaded their sample, and it runs perfectly using the data setup they provide in the sample database. (by the way, thanks for pointing out that link, the sample db has some pretty handy things avail. in it)

Since it runs fine for me, I'm wondering if you have a broken reference in the combo boxes that set the filter values, and/or a data type mismatch problem in the fields you are trying to sort on.

Please check each one carefully to make sure your combo boxes match exactly the fields on the report, check to make sure you have each field matched to the data it is sorting (i.e. that you haven't tried to pass a string value to a number field on your report.)

Post back to let me know how it goes.

Regards,
Scott
Aug 16 '07 #10

P: 21
Thank Scott it is working like a charm now i had to change a little in the code u gave me and know it look like this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr$(34) & Me("Filter" & intCounter) & Chr$(34) & " And "

Thanks again
Aug 16 '07 #11

Scott Price
Expert 100+
P: 1,384
Thank Scott it is working like a charm now i had to change a little in the code u gave me and know it look like this:

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr$(34) & Me("Filter" & intCounter) & Chr$(34) & " And "

Thanks again
Glad it's working for you!

Regards,
Scott
Aug 16 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.