Pick start/end date of multiple possible fields on one form

[rgrantz]

I have a form w/ 2 OLE calendar controls on it; the form source is a query
that has criteria set in a certain date field to be between the 1st calendar
control and the second calendar control. When the user changes either the
start date or end date, the form requeries and displays the new data in a
subform (although I have to use the records-->refresh menu item after
choosing a date, because the AfterUpdate doesn't seem to fire for some
reason).

What I would LOVE to do is have a combo box that the user can use to choose
WHICH field needs to be between the 2 dates.

For instance, each record has several dates (date order was entered, date it
was processed, date it was shipped, etc.). I would like for the user to be
able to choose "Order Entered" from the combo box, THEN choose the start and
end date, and the subform would show records that had been entered between
those 2 dates. BUT, the user can also choose "Date Shipped" from the combo
box, and then the records would be requeried to show those with a Ship Date
between the 1st and 2nd calendar controls.

Is there an easy way to do this with VBA or the query builder?

Thanks for any help.

 

[Dale Fye]

The way I usually do this is to create a single subform, but change the
underlying query. Then, in the AfterUpdate event of the combo box (or the
AfterUpdate event of the Start and End date fields) , I would change the
RowSource for the subform, although this method only works if you want your
user to see the same fields, but base the query on different fields. If, on
the other hand, you want to display different data depending on which option
the user selects in your combo box, then create several subforms, each with
its own query. Then, in the AfterUpdate events I would change the
SourceObject value of the subform control to the form you want to use.

If you need more help, let me know.

 

[rgrantz]

Dale:

Thanks for the help. I was applying this method and then ran into some
confusing roadblocks. The guy I'm trying to help is currently using Excel
to do all this, and in addition to that being ridiculous, it adds about 45
minutes of data entry to 4 people's workday, and duplicates a lot of work.
It seems to me that what I'm trying to do is probably a pretty common goal
for Access users. If you could help me with this I'd greatly appreciate it,
and can buy you lunch. I have no problem emailing or ftping my application
thus far, either. Below is the post I had regarding the whole deal:

I'm trying to duplicate Excel's Autofilter functionality. In Excel, for
every column's autofilter criteria you choose, the more the records are
filtered. However, when you choose "All" in one column, the records are
"unfiltered" for that particular criteria, but still using the other
columns' criteria. Thus, you can continue to add or subtract criteria on
the same page.

In Access, I have a form w/ a subform. On the form, I have several unbound
controls that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source is the
query) continue to "drill down" as the unbound controls are filled in
(similar to the effect of the Autofilter arrows used in Excel).

For example, when the user enters "ABC" in the "CustomerName" unbound
control, the records refresh to show only those w/ customer name of "ABC."
When user then enters "10/12/04" in the "OrderDate" unbound control, the
records are further filtered to show only Customer Name of "ABC" with orders
on 10/12/04. What I would like is when the user then deletes "10/12/04"
from the OrderDate control, the records refresh to show all the ABC orders
again, deleting the criteria and so showing more records. Right now what
happens is, since the criteria in the query grid is set to the value of the
unbound control, Access looks for records with "Null" value when the
criteria is removed in the control (instead of all values, with no criteria)

Now, I got an MVP's suggestion for the following:

WHERE ((fieldA = Forms!FormName!UnboundControl1) OR
(Forms!FormName!UnboundControl1 Is Null))
AND ((fieldB ...

This worked for about 4 criteria fields. However, the number of criteria
I'm using makes Access return a "too complicated" error on the criteria, and
in addition, I could no longer open the query in design view, and so had to
start over.

This guy I'm making this for has about 13 different criteria he likes to
change and see new data for (cutomer name, who entered it, date range it was
received, date range entered, date range processed, who processed it, what
machine it was made on, who packaged it, who shipped it, etc. etc.).
Currently he uses a combination of the Autofilter utility and the
Dcount(Data!...etc.) formula in Excel, which returns data based on any
criteria put in an entire row, which is nice. He can change any criteria
along the entire row, adding some, changing some, deleting others, etc., and
Excel just uses the whole row to continuously dynamically change the records
returned as the criteria change.

Can't this be done somewhat easily in Access? Like, can't you make a
criteria in the Query Builder say "If control is empty, return all records,"
(kind of like "IIF([Forms]![FormName]![ControlName] = "", *,
[Forms]![FormName]![ControlName])", except this doesn't work).

I use the query builder, not SQL, so if someone could help me out with this,
I would appreciate it. Everything about this reporting thing is tailor-made
for Access, but if I can't duplicate this guy's ability to change (or
delete) 13 different criteria dynamically, he's keeping it in Excel (which,
by the way, would add about 1200% more time to duplicative data entry around
here, which is why I want to make this thing in Access). I'm not married to
using a form w/ unbound controls and a subform w/ the query results. I'm
not to married to anything, except that this guy needs to be able to change
10 (or 13) different criteria around, and after each change see the new
results on the same page. The ONLY problem here is that deleting a criteria
tells Access to look for Null in that field; I want a blankc criteria field
to mean "no criteria for THIS field" I do dabble w/ VBA, so if someone has
code snippets for this kind of thing, I can probably do it that way too.