userfriendly filter?

[thelonelyghost]

I'm working on a filter to go through thousands of records to display any that apply to a given set of criteria. With that many records, it makes no sense to search through each individually so I have the SQL code all set up and the fields are prepped, but I don't know how to:

1. run SQL through VBA,
2. link the form inputs (i.e. text, combo box, radio button, check box), and
3. output the select statement to a subform, viewed as a table.

Any ideas? I would really prefer that my code be in VB so it's that much closer to being idiot-proof, for the idiot who decides to venture into the settings sections. My point in designing it this way (with a subform as a table) is to make this form both temporary and read-only. If there is an easy alternative available, I'd be happy to hear it as well.

I'm using Access 2000 and a lot of guides available are only for 2003 or 2007. Before you tell me 2003 and 2000 are very similar, I know that. I also know that there are enough key differences that it requires a more expertise than I possess to spot the inconsistencies between the two.

Some sample of my code is:

'Declaring String to be referenced as a single SQL query'
Dim SQLContent As String
  SQLContent = ""
Dim SQLFormula As String
  SQLFormula = "SELECT * FROM [Return Log] WHERE " & SQLContent & " GROUP BY [Return Log].[" & GroupBy & "]"

'Whenever any field is updated, run query again.  Negates need for a "submit" button on form'
Private Sub Form_AfterUpdate()

'Declaring Variables'
Dim OldLogNumber As Single
Dim NewLogNumber As Long
Dim ReportSentTo As String
Dim CustPartNum As String
Dim MinDateR As Date
Dim MaxDateR As Date
Dim MinDateC As Date
Dim MaxDateC As Date 

...

'Setting Form inputs to variables for easier reference, trimming space characters from      '
'before/after string, and Setting all empty inputs to zero-length instead of null.          '
  OldLogNum = Me.OldLogNumber
    If OldLogNum Is Null Then OldLogNum = ""
    Else: OldLogNum = Trim(OldLogNum)
    SQLContent = SQLContent & "[Return Log].[OldLogNumber] = '" & OldLogNum & "' AND "
    End If
  NewLogNum = Me.NewLogNumber
    If NewLogNum Is Null Then NewLogNum = ""
    Else: NewLogNum = Trim(NewLogNum)
    SQLContent = SQLContent & "[Return Log].[NewLogNumber] = '" & NewLogNum & "' AND "
    End If
  ReportSentTo = Me.ReportSentTo
    If ReportSentTo Is Null Then ReportSentTo = ""
    Else: ReportSentTo = Trim(ReportSentTo)
    SQLContent = SQLContent & "[Return Log].[ReportSentTo] = '" & ReportSentTo & "' AND "
    End If
  CustPartNum = Me.CustomerPartNumber
    If CustPartNum Is Null Then CustPartNum = ""
    Else: CustPartNum = Trim(CustPartNum)
    SQLContent = SQLContent & "[Return Log].[CustomerPartNumber] = '" & CustPartNum & "' AND "
    End If
  MinDateR = Me.MinDateReceived
    If MinDateR Is Null Then MinDateR = ""
    Else: MinDateR = Trim(MinDateR)
    SQLContent = SQLContent & "[Return Log].[DateReceived] >= '" & MinDateR & "' AND "
    End If
  MaxDateR = Trim(Me.MaxDateReceived)
    If MaxDateR Is Null Then MaxDateR = ""
    Else: MaxDateR = Trim(MaxDateR)
    SQLContent = SQLContent & "[Return Log].[DateReceived] <= '" & MaxDateR & "' AND "
    End If
  MinDateC = Me.MinCompletionDate
    If MinDateC Is Null Then MinDateC = ""
    Else: MinDateC = Trim(MinDateC)
    SQLContent = SQLContent & "[Return Log].[CompletionDate] >= '" & MinDateC & "' AND "
    End If
  MaxDateC = Me.MaxCompletionDate
    If MaxDateC Is Null Then MaxDateC = ""
    Else: MaxDateC = Trim(MaxDateC)
    SQLContent = SQLContent & "[Return Log].[CompletionDate] <= '" & MaxDateC & "' AND "
    End If

...

  'Clean up the concatenation of all of the fields into a SQL statement'
  SQLConnect = Replace(SQLConnect, " AND ", "", (Len(SQLConnect) - 7), -1)
  
End Sub

[jimatqsi]

I think these posts will help

http://bytes.com/topic/access/answers/889740-add-filters-form

http://bytes.com/topic/access/insights/590551-example-filtering-form

Jim

[thelonelyghost]

wow! I can't believe I didn't find this before! I swear I tried my hardest to search the forums before posting, especially since I always get peeved if a new member posts something that's been asked and answered MANY times previously.

The first one links to the second, but still provides some useful info along the way for Access beginners like me. For instance, I didn't know that a "_" told the module that the line's instructions continue on the next physical line.

Thanks again, Jim(atgsi). I really appreciate it!

[jimatqsi]

You're quite welcome.

I am not a big fan of the Bytes.com search routine. What works better (sometimes) is the Google advanced search. I just tell Google to search only the bytes.com domain and that works really well.

Jim