473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Filtering with access

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
11 1979
kestrel
1,071 Expert 1GB
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
404 Expert 256MB
Hi All,

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

Regards Purple
Aug 15 '07 #3
Scott Price
1,384 Expert 1GB
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
Iyhamid
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
1,384 Expert 1GB
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
Iyhamid
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
Iyhamid
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
Iyhamid
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
1,384 Expert 1GB
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
Iyhamid
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
1,384 Expert 1GB
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

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

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: Elias Farah | last post by:
Hello All, I hope someone can give me (and other keen access enthusiasts) some helpful information to explain how to most efficiently filter Queries & subqueies. Consider this common simple...
0
by: Keith Shearer | last post by:
I'm having a bit of trouble, moving between controls, when filtering on a form. I'm using a continuous form. At the top I have 2 date fields. I want to filter the data specified between the two...
5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
5
by: sensible | last post by:
Can I solve this problem using Access? If so, will some give this newbie a simple step by step on how to go about it, please....all the way from File/New. In Excel I am using...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
3
by: Shawn Ramirez | last post by:
As with most web applications speed is a huge deal to me in my applications. My customers don't really care if my app is a true 3 tier application or not, they just want it to be faster then it was...
2
by: Jimmy | last post by:
Hi! I'm looking for a library with methods for filtering user input in a web application. I mean, some API which gives more functionality than the traditional .NET validation controls. Any...
0
by: Yarik | last post by:
Hello, Here is a sample (and very simple) code that binds an Access 2003 form to a fabricated ADO recordset: ' Create recordset... Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset '...
0
by: Lyn | last post by:
I have a problem using the form .Filter and .FilterOn properties which causes Access to crash (as detailed in a separate post). The form operates in continuous mode, displaying matching records...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.