Filter or WHERE

[BruceM]

I'm trying to come up with a strategy for limiting the initial recordset to
just the records that have been completed, but allowing the user the option
of viewing all records. This intial recordset would be for all departments,
but I would like to give the user the option of limiting the recordset
according to department. Further, if the user selects all records, there
should be an option of limiting the recordset according to a selected date
range.
I have spoken so far of limiting the recordset rather than filtering it
because I am not sure filtering is the best approach. The database will
generate fewer than 100 records per year, so even though filtering can be
slower than using a WHERE clause, I doubt I will notice much difference.
The form is based on a query that incorporates several tables, including
three separate instances of the same table. The query's SQL is
approximately 1600 characters in length. If I was to use a WHERE clause, I
could either make the SQL string (without a WHERE clause) into a constant
(conSQL), or I could use an asterisk, which I have heard is not such a good
idea.
I can build up the WHERE string using something like the system Allen Browne
described on his web site for filtering a report based on selections in a
multi-select list box, except that I would be using several separate
controls to build the filter. That string (strWhere) could be used either
for filtering or as a SQL WHERE clause. For the SQL, a command button's
Click event could have something like:

dim strSQL as String

strSQL = conSQL & strWhere
Me.Recordsource = strSQL

With an asterisk:

strSQL = "SELECT * FROM qryMain " & strWhere
Me.Recordsource = strSQL

I would in like manner apply a WHERE clause in the form's Open event to
limit the records to ones in which the DateFinal field is null.

Or I could use a filter. In the form's Open event:

Me.Filter = "DateFinal Is Null"
Me.FilterOn = True

Once the form is open, I would build strWhere as described, and use that as
the filter upon demand.

By the way, I can't quite get my mind around why using the asterisk is
considered a bad idea. Maybe I have taken the warning too literally, and
there are circumstances in which it makes sense. Without the asterisk, if I
change the query I will need to make a new SQL string to use as the basis
for the recordsource (if I go that route).

To sum up, I wonder if there are criteria upon which to base the decision to
use either SQL or a filter.

 

[Albert D. Kallal]

To sum up, I wonder if there are criteria upon which to base the decision
to
use either SQL or a filter.

Well, the way I approach this is:

I use the "where" clause to restrict records when you open a form. This is
the MOST common way I wind up setting the records for a form. The "where"
clause of the open form is just that...*only* available at openform time.

It is *better* to use a where clause in place of opening up a form, and
*THEN* setting the filter. So, it stands to reason that the filter method of
a form is really for forms that ALREADY have a reocord set, and are already
open.

So, filter = for form already loaded and has records
"where clause" = for restricting the records when you load the form

Now, your case is that you want to actually "stuff" the sql right into the
forms recordsource, and there is nothing wrong with that at all. If the form
is already opened, and has records, if the filter *then* is to be a sub-set
of the already loaded records, then use the filter. If the records are
complete different, then just stuff in the sql as you have.

As for using the "*"....I don't see a problem....

 

[Guest]

Hi, Bruce.

I would go with the Filter technique. It would be less coding and easier to
control (IMHO). As to the * thing, I haven't heard that before, but then
there is a lot I haven't heard

 

[BruceM]

Thanks to both Albert and Dave for the replies. I remember what it was
about the asterisk, which is that it is generally best to avoid it in the
query itself (when selecting all of the fields from a table).

So, I can set the recordsource when loading the form (if Load is the best
event):
Me.RecordSource = "SELECT * FROM qryMain WHERE DateFinal Is Null"

Then, I can build the filter string based on selections in various controls.

The thing I wonder about, though, is that while most of the time the records
of interest are the ones that have not been completed, at times it will be
necessary to view all records, including ones that are complete (DateFinal
has a value). The recordsource when the form is loaded is limited to
records in which DateFinal Is Null (that is, incomplete records). However,
at times it will be necessary to view all records. In that case there is no
filter to remove, so the recordsource will need to be redefined:
Me.RecordSource = "SELECT * FROM qryMain"

If department and date range are used as filters, limiting/delimiting the
recordset would involve both requerying the recordsource and applying the
filter. A toggle button for complete/incomplete records would redefine the
recordsource, which would be the recordset upon which the filters
(department and date range) would work. If I filter for a particular
department, then click the toggle button to redefine the recordsource, will
the filter persist? If I am looking at incomplete records for the Shipping
department, then click the toggle button to look at all records, I expect
the filter would remain and I would be looking at all Shipping records
(complete and incomplete), but I'm not sure.

I expect I'm making too much of this, but I will be able to apply what I
learn here to other projects, so I am exploring in greater depth than may be
warranted in the current situation. I expect that I could limit the
recordsource when loading the form, and remove the limit with the toggle
button, then use either a filter or a redefined recordsource for the
department and date range, whichever if simplest to implement.

 

[Albert D. Kallal]

Well, if your actually setting the forms source, then why load the form with
a query that resolves to no records? Why not leave it blank???

 

[BruceM]

I didn't mean to suggest the query would return no records. The query that
I am calling qryMain returns all records, and it provides some concatenated
fields and other features not available in a table. I can either use
qryMain as the form's Record Source property, or I can set the recordsource
in the form's Load event:
Me.RecordSource = "SELECT * FROM qryMain WHERE [DateFinal] Is Null"

DateFinal is the last field that gets filled in. It is filled in when no
more work needs to be done on the record. It is theoretically possible that
all of the records would be completed, so I should allow for the possibility
of no records, but most of the time at least 10 records are not complete.

In some cases it will be necessary to look at all of the records, including
completed ones:
Me.RecordSource = "SELECT * FROM qryMain"

Also, I need the ability to look at records within a date range or by
department. One way is to build the WHERE clause as strWhere, and append it
to the recordsource:
Me.RecordSource = "SELECT * FROM qryMain " & strWhere

strWhere would also include whether or not DateFinal is null.

Another option is to use strWhere as the filter:
Me.Filter = strWhere
Me.FilterOn = True

I'm not sure offhand if I would have to construct strWhere differently for a
filter than for recordsource SQL, but in any case I would have to build
strWhere.

Here are the choices, as I see it:
1) I could use qryMain as the form's Record Source property, filter it as
soon as I load it to exclude completed records, and filter as needed for
department, date range, and whether or not the records are completed

2) I could reset the form's recordsource each time I need a different
recordset (instead of filtering)

3) I could set the recordsource when the form loads:
Me.RecordSource = "SELECT * FROM qryMain WHERE [DateFinal] Is Null"
Once the form is loaded, I would need to reset the recordsource if I want to
return all records (by removing the WHERE from the SQL), and could use
filters for department and date range.

I would prefer either 1) or 2) because it would all be done the same way,
rather than resetting the recordsource for some things and filtering for
others. I think I would rather use 2), unless there is a specific reason I
shouldn't.

 

[Albert D. Kallal]

I didn't mean to suggest the query would return no records.

Ah,...my bad, I read that wrong....

Here are the choices, as I see it:
1) I could use qryMain as the form's Record Source property, filter it as
soon as I load it to exclude completed records, and filter as needed for
department, date range, and whether or not the records are completed

Hum, ok. You likely should load up the form with a query, or where clause.
It does not make sense to filter *after* you load the form. (unless the
un-filtered display/view is needed...

I would prefer either 1) or 2) because it would all be done the same way,
rather than resetting the recordsource for some things and filtering for
others. I think I would rather use 2), unless there is a specific reason
I shouldn't.

...yes, that approach seems just fine to me...

 

[Aaron Kempf]

I only ever load a single record at a time

everything that I do is a single record
so I never need to use Filter

I do sometimes use the ServerFilter filter; for example Data Access Pages

 

[BruceM]

I wouldn't like to load the individual lines in a bank statement one record
at a time. I would want to load all of the records for a month or whatever,
so I can see them arranged in chronological (or whatever) order.
In my case, each record is about a course of action that needs to be taken
regarding a reported problem. It makes the most sense to me to load the
records that are still open.

 

[BruceM]

Albert, thanks for taking the time to look at this. It is the case that the
filtered recordset (only the records still needing attention) will be the
most commonly used was of looking at the records, but the unfiltered
recordset will be needed at times.

I think I will go ahead and redefine the recordset by building a WHERE
clause according to combo box selections, etc. I expect that either a
filter or a redefined recordset will work for me. It may be that I decide
in the future to take another approach with a different project, but as I
said, it sounds like I'll be OK with this one.

Thanks again.