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

Search/Filter Working but has some error

P: 14
Hi! I have a code..
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command18_Click()
  2. 'Update the record source
  3.     Me.SMS_ValidateSubform.Form.RecordSource = "SELECT * FROM SMS " & BuildFilter
  5.     ' Requery the subform
  6.     Me.SMS_ValidateSubform.Requery
  7. End Sub

Expand|Select|Wrap|Line Numbers
  1. Private Function BuildFilter() As Variant
  2.     Dim varWhere As Variant
  3.     Dim varColor As Variant
  4.     Dim varItem As Variant
  5.     Dim intIndex As Integer
  7.     varWhere = Null  ' Main filter
  9.     ' Check for LIKE First Name
  10.     If Me.Text16 > "" Then
  11.         varWhere = varWhere & "[Card_Number] LIKE """ & Me.Text16 & "*"" AND "
  12.     End If
  15.     ' Check if there is a filter to return...
  16.     If IsNull(varWhere) Then
  17.         varWhere = ""
  18.     Else
  19.         varWhere = "WHERE " & varWhere
  21.         ' strip off last "AND" in the filter
  22.         If Right(varWhere, 5) = " AND " Then
  23.             varWhere = Left(varWhere, Len(varWhere) - 5)
  24.         End If
  25.     End If
  27.     BuildFilter = varWhere
  29. End Function
Its working on my first project. but when i use it on my new project which is the same with my first project. It has a problem regarding on filtering. When I search/filter on my new project, it only show the first record either I put a "Card_Number" on that or it is Null.

I can't see any error on my code. but I'm confuse about what's happening when I'm searching/filtering a record.

Thanks in advanced (~.^)olll
Mar 15 '12 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 3,282

I may have found some problem areas in your filter string generation, but I will also make a few recommendations for you, which may sound picky, but may also help in the long run.

First, I would suggest using appropriate names for your controls, such as naming the command buttons according to what they do, and naming text boxes according to what data they contain. This helps you (and perhaps anyone coming after you) understand what any particular control is for. See my minor changes below....

Second, I'm not sure why you are declaring your function results and variables as Variants. This sets aside undue resources for essentially an "unknown" type of variable, then changes the data types once it determines what is going into that variable. Since filters are strings, set your variable types for the filter text as strings.

Also, "it appears" that this code is just looking at the card number on the form, then filtering the records by that card number (i.e. in a search box???) plus additional variables from the form? If this is the case, I have modified the code below based on that presumption. If this is not the case, please clarify, because you have your filter appending an additional "AND" that never needs to be there in the first place, then removing it again. However, I do notice several other variables listed, so I've also inferred some code to assist.

Your Filter was missing the "WHERE" required for the SELECT statement. See below for how this was added. It was also missing the final semicolon ";", which is required for every SELECT statement.

I think I've captured the essence of what you need below, but please clarify if I've missed the mark.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub cmdFilterRecords_Click() 
  5.     'Update the record source
  6.     Dim strFilter as String  'I prefer to establish a string for this, then set its value based on the function, although it's not required, you get the same results
  7.     strFilter = BuildFilter
  8.     'Keep in mind that this will return ALL records if the form is blank....
  10.     Me.SMS_ValidateSubform.Form.RecordSource = "SELECT * FROM SMS " & strfilter & ";"
  11.     ' Requery the subform -- this is not required to requery because you have changed the Record Source...
  12.     Me.SMS_ValidateSubform.Requery 
  13. End Sub
  15. Private Function BuildFilter() As String
  16.     'I would remove all these variables, unless you somehow use them elsewhere in the code
  17.     Dim varWhere As String
  18.     Dim strColor As String 'as an example
  19.     Dim intItem As Integer 'as an example
  20.     Dim intIndex As Integer 
  22.     BuildFilter = ""  ' Reset the Main filter--I don't like setting to Null 
  24.     ' Check for LIKE First Name - card number must be TEXT, this assumes you only have the beginning characters of the card in the form.  This should probably use an "=" instead of "LIKE" and remove the "*"
  25.     If Me.txtCardNumber <> "" Then 
  26.         BuildFilter = " WHERE [Card_Number] LIKE '" & Me.txtCardNumber & "*'" 
  27.     End If 
  29.      ' Check for LIKE Second Name -- MUST BE TEXT
  30.     If Me.txtColor <> "" Then
  31.         'This nested If makes sure we always have a WHERE in the statement or just adds to it
  32.         If BuildFilter = "" Then
  33.             BuildFilter = " WHERE [Color] = '" & Me.txtColor & "'" 
  34.         Else
  35.             BuildFilter = BuildFilter & " AND [Color] = '" & Me.txtColor & "'" 
  36.         End If
  37.     End If
  39.      ' Check for LIKE Third Name -- for INTEGER variable types
  40.     If Me.txtItem <> "" Then 
  41.         If BuildFilter = "" Then
  42.             BuildFilter = " WHERE [Item] = " & Me.txtItem
  43.             'Notice no single quote in this expression 
  44.         Else
  45.             BuildFilter = BuildFilter & " AND [Item] = " & Me.txtItem 
  46.         End If
  47.     End If 
  49.     'I would remove this entire section of code below....
  50.     ' Check if there is a filter to return...  
  51.     If IsNull(varWhere) Then  
  52.         varWhere = ""  
  53.     Else  
  54.         varWhere = "WHERE " & varWhere  
  56.         ' strip off last "AND" in the filter  
  57.         If Right(varWhere, 5) = " AND " Then  
  58.             varWhere = Left(varWhere, Len(varWhere) - 5)  
  59.         End If  
  60.     End If  
  61. End Function
I hope this helps. SELECT statements based on variables can be tricky, so it might be wise to cycle through the teesting, one variable at a time, and use Debug.Print to see what your Filter String looks like throughout the stages of its development until you achieve your desired results.

As always, glad to offer additional help if you hit snags.
Jul 26 '12 #2

Post your reply

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