RE: Need Help with filtering in queries

Tech-Archive recommends: Speed Up your PC by fixing your registry

I have a query called Qrpt201Summary which totals on division and order.
This query can return thousands of rows without a date range criteria added
to it. My main filter screen prompts user to enter the begin date and
end date and using code I will open the report and set the date range in
the query as follows:
strWhere = "[firstofgl] is not null and [firstofgl] Between #" & frm!
BeginningDate & "# AND #" & frm!EndingDate & "#"
DoCmd.OpenReport txtReportName, PrintMode, , strWhere

The above works fine. I also have code that checks if a division was
selected or if a customer was selected and adds SQL to the strWhere before
opening the report. That also works great. The problem I have is in the
building of the division and customer combo boxes. I only want the
division list and customer list to be the distinct values for the given
date range. Currently I am using the queries below to build the combo
boxes and the form takes a few seconds to open and that is using local
tables. I will be working with tables on a Share network drive. I really
need to get the queries below to be more efficient.

I have a division combo box with a recordsource as follows:

SELECT "00" as div_code," ALL" AS div_name from Qrpt201
UNION SELECT distinct Qrpt201.div_code, Qrpt201.div_name
FROM Qrpt201
ORDER BY div_name;
The default value of the combo box is "00"

Query Qrpt201 simply selects fields division, customer, shipdate, order
date, etc. with no totaling.

I then have a customer combo box which should display only the customers
for the selected division. If all is selected for division, then distinct
customers for all divisions will be displayed in the combo box. Here is
the query:

SELECT "00" as SDDAN8," ALL" AS ABALPH from Qrpt201
UNION SELECT Distinct Qrpt201.SDAN8 ,Qrpt201.ABALPH
FROM Qrpt201
WHERE
Qrpt201.div_code=IIf([cboDivision] ="00",[div_code],[cboDivision])
ORDER BY ABALPH;
I think what I need to do is include in the above two queries where date
between form!begindate and form!enddate.

If this makes sense and there is a better way to do this, please let me
know. Thanks.
Karen
.

Relevant Pages

  • Re: Calculate a percentage using 2 different query totals
    ... Duane Hookom ... It is not about using the Query Wizard because you will ... > queries from incompatible record sources. ... >> field names like [Customer ID]. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Searching Records
    ... Next, build a set of queries, one for each search ... the particular field the query will search on. ... design a form to display the output of one of the ... >> Selection, Filter by Form, and Apply Filter. ...
    (microsoft.public.access.gettingstarted)
  • Re: Combo box action
    ... recordsource query. ... > Likely your problem is that the combobox is bound. ... >> form is a combobox for the customer ID. ... >> names is displayed in the combo box and the address fields are display ...
    (microsoft.public.access.forms)
  • Re: Calculate a percentage using 2 different query totals
    ... field names like [Customer ID]. ... By means that when you display the results of the query ... Duane Hookom ... > In Regards to the 2 queries, please clarify what you mean by. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Need Help with filtering in queries
    ... I think you are correct about filtering by date in the last queries, ... Calculations in queries slow it down. ... > I have a query called Qrpt201Summary which totals on division and order. ... > building of the division and customer combo boxes. ...
    (microsoft.public.access.formscoding)