[AccessD] Simulate AutoFilter

Thomas,

I've done this many times, and it takes a lot of repetitive code, but it
does work well.

An Example:

    '-- SELECT CASE BASED ON A COMBOBOX
    '-- cboStage IS THE FIRST FIELD IN THE LIST
    If Not IsNull(cboStage) Then
        Select Case cboStage
            Case "All"
                stgSQL = "Stage IS NOT NULL"
            Case "Open"
                stgSQL = "DateClosed IS NULL"
                blnFirstControl = True
            Case "Description"
                stgSQL = "Stage = 'Description'"
                blnFirstControl = True
            Case "Root Cause"
                stgSQL = "Stage = 'Root Cause'"
                blnFirstControl = True
            Case "Implement/Verify"
                stgSQL = "Stage = 'Implement/Verify'"
                blnFirstControl = True
            Case "Closed"
                stgSQL = "DateClosed Is Not Null"
                blnFirstControl = True
        End Select
    End If
    
    '-- TEXT BOX OR COMBOBOX   
    '-- ALL SUBSEQUENT FIELDS USE blnFirstControl 
    If Not IsNull(cboType) Then
        If blnFirstControl = True Then
            stgSQL = stgSQL & " AND Type = '" & cboType & "'"
        Else
            stgSQL = "Type = '" & cboType & "'"
            blnFirstControl = True
        End If
    End If

    '-- PARTIAL SEARCH FIELDS    
    stgComments = "*" & txtComments & "*"
    If Not IsNull(txtComments) Then
        If blnFirstControl = True Then
            stgSQL = stgSQL & " AND Comments Like '" & stgComments & "'"
        Else
            stgSQL = "Comments Like '" & stgComments & "'"
            blnFirstControl = True
        End If
    End If

    '-- DATES RANGES
    If Not IsNull(txtDateReportedAfter) Then
        If blnFirstControl = True Then
            stgSQL = stgSQL & " AND DateReported >= #" &
txtDateReportedAfter & "#"
        Else
            stgSQL = "DateReported >= #" & txtDateReportedAfter & "#"
            blnFirstControl = True
        End If
    End If

    '-- FINISH THE SEARCH CRITERIA STRING
    stgSQL = "WHERE " & stgSQL


HTH,
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart
Sent: Wednesday, April 04, 2007 12:49 PM
To: ewaldt at gdls.com
Cc: accessd at databaseadvisors.com
Subject: Re: [AccessD] Simulate AutoFilter

Even on their worst days, the engineers at YPG did not want
to search 1.5 mil records on a single screen.

If they want to filter based on all 40 columns, then add
combo boxes for all of them. Then build the SQL for the
subform source dynamically based on the comboboxes that
have been used to select data. It will be a lot of code-behind,
but it will never break like the Excel thing did.


At 12:41 PM 4/4/2007, you wrote:

>Thanks. My sanity is questionable at best.
>
>I would never have recommended Excel for this job. The department 
>asking for help actually used Excel for this before I got here. A 
>guy put together some VBA to import the text data into Excel, 
>splitting it into several sheets, of course. He has left, and the 
>VBA now bombs (no pun intended). Since it no longer works, and since 
>it took 25 minutes to load when it did, they've asked for my help.
>
>Limiting them in any way but their own applied filters is not an option.
>
>If I can't find a way to fake an AutoFilter, I guess I'll use a 
>zillion or so comboboxes and go from there.
>
>Thomas F. Ewald
>Stryker Mass Properties
>General Dynamics Land Systems
>
>
>
>
>
>
>Thomas,
>
>First, are you nuts? Loading 1.5 million records just to filter them.
>That is extremely poor design.
>
>And to even consider Excel for the task...well, I won't say what I
>think about that.
>
>Build a search form.  Create a subform on it that will display the
>results of the things selected as part of the search. Do not load
>the source for the subform until you click on a button to 'find' the
>results of the search criteria entered. As part of the search, give
>them a field for the top X number of records. And do not let them
>exceed a certain number.
>
>Even you military contractor types should be able to use it. :-)
>
>I can say that because I developed the FRACAS system that Stewart
>& Stevenson used in the FMTV production system for the Army. So, I
>know how backward engineers can be. I also worked at YPG as a RAM-D
>engineer. As well as reporting on some of the testing on the M1A1
>systems.
>
>Robert
>
>At 12:00 PM 4/4/2007, you wrote:
> >Date: Wed, 4 Apr 2007 10:27:16 -0400
> >From: ewaldt at gdls.com
> >Subject: [AccessD] Simulate AutoFilter
> >To: accessd at databaseadvisors.com
> >Message-ID:
> >
> ><OFA16E1530.228F9136-ON852572B3.004F0FEB-852572B3.004F6677 at is002023 
> .gdls.com>
> >
> >Content-Type: text/plain; charset="US-ASCII"
> >
> >I'd like to create a form in Access which simulates the AutoFilter
> >functionality in Excel. Basically, it's the size of the files (1.5
million
> >records) that makes Excel less useful than it would be otherwise. I doubt
> >the company wants to spring for Office 2007 just now to have Excel's
> >capacity increase.
> >
> >Does anyone know of a sample database or just some instruction on how to
> >build such a puppy? I've got about 40 fields; will this require 40  combo
> >boxes, or is there another way?
> >
> >TIA.
> >
> >Thomas F. Ewald
> >Stryker Mass Properties
> >General Dynamics Land Systems
>
>
>
>This is an e-mail from General Dynamics Land Systems. It is for the 
>intended recipient only and may contain confidential and privileged 
>information. No one else may read, print, store, copy, forward or 
>act in reliance on it or its attachments. If you are not the 
>intended recipient, please return this message to the sender and 
>delete the message and any attachments from your computer. Your 
>cooperation is appreciated.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com