Reports ask for date range 2 times

K

kevin

I modified the Northwind example database to track a Accounts
Receivable application. So far everything is working well. I need to
make a few modifications that I will get to below. A problem( not
really but an annoyance) is when I run reports I get the dialog to
enter date twice whenever I run I report that tracks employee
commissions.

First it asks for parameter value for Beginning date, then End date:
then
it asks for the Forms!Sales by Year Dialog!BeginningDate and Forms!
Sales by Year Dialog!EndingDate

I don’t know where these get generated from so I don’t know how to
eliminate one.

I also would like to add a parameter to choose only records that have
a check box field checks for PAID.
(the paid checkbox is on the form and in the table ORDERS.)

I am thinking that it may be easier to create or modify the "Sales by
Year Dialog" form to pass whatever variables I need to ALL reports.

Is there a way to have access use the SALES DIALOG and only the sales
dialog for reporting parameters?

So far the only additional reports required are:
EMPLOYEE COMMISSION
(a report that is generated by product sale - freight based on
commissionable amount that varies between 0 and 50 percent)

This works well now but I have a issue that need addressing. I have a
field in orders for commissionable amount and percent commission but
the commission amount is calculated in the report and not stored in
the table ORDERS. This is a problem b/c if a partial payment is made
is messes up the commission.

CUSTOMER AGING
I would like to get the report to group 0-30, 30- 60, > 60 if it isnt
too complicated. My biggest issue is the check box PAID thing to keep
paid invoices from appearing in the report.

This is the same kind of thing I need for commission paid report .
2 Fields INVOICE PAID and COMMISSION PAID in ORDER TABLE and in ORDERS
form to track info.

I would also like to get this report to run for single customers as
well as all customers.

Please Keep in mind I have only been using ACCESS for about 2 weeks if
you decide to help....... LOL
( I am way over my head)

Thanks

Kevin
 
K

Ken Sheridan

Kevin:

1. The report's underlying query in the Northwind database has the
following WHERE clause:

WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between
[Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year
Dialog]![EndingDate]));

In query design view the expression is in the criteria row of the
ShippedDate column.

As you see this includes the parameters [Forms]![Sales by Year
Dialog]![ShippedDate] and [Forms]![Sales by Year Dialog]![EndingDate]

If the form is not open when the report is opened it will prompt for these
parameters. They differ slightly from the ones you've cited, so you've
either changed them in your report or you've not posted them quite correctly
here.

The other two parameters which you are being prompted for, Beginning date
and End date, are not used by the Northwind report, so I can only assume
you've added these yourself.

I'd suggest you do as you are thinking and create a new general purpose
dialog form similar to the Northwind form, calling it Sales Dialog, change
the names of the controls on it to BeginningDate and and EndingDate, and
change the parameters in the reports query to:

[Forms]![Sales Dialog]![BeginningDate]

And:

[Forms]![Sales Dialog]![EndingDate])

Remove any other Beginning date and End date parameters from the query.

2. Add a check box chkUnpaid, caption something like 'Unpaid invoices
only:' to the new dialog form and in the report's query put the following in
the criteria row of the Paid column:

Not [Forms]![Sales Dialog]![chkUnpaid]

3. To use the same dialog form for multiple reports add separate buttons to
the dialog form, one to open each report, and caption them accordingly. To
open any report open the dialog form first and open the report from this,
rather than opening the report directly. It is possible to open the report
first and have the report open the dialog form, but with your present
experience you'll probably find opening the form first and using separate
buttons for each report simpler. Another approach is to have a list box of
available reports on the dialog form so that you can open one or more reports
simultaneously, but again this is perhaps a little too advanced for you at
present. We can com back to it if you wish.

4. An expression to compute the pro rata commission on a partial payment
would be along these lines:

= ([AmmountPaid] / [CommisionableAmount]) * [ CommisionableAmount] *
[CommissionRate]

where CommssionRate is expressed as a fractional value, e.g. 0.1 for 10
percent, 0.25 for 25 percent etc.

5. To group outstanding debtors use the Switch function in a computed
column in the underlying query, e.g. by entering the following in the 'field'
row of a blank column in the query design grid:

DebtorGroup:Switch(Date() – [DueDate] <= 30, 1, Date() – [DueDate] > 30 And
Date() – [DueDate] <= 60, 2, Date() – [DueDate] > 60, 3)

where DueDate is a column in the table of date/time data type. You can then
group the report on DebtorGroup.

6. To select a single customer add a combo box, cboCustomer, to the dialog
form set up as follows (I'm using the Northwind Customers table as an example
here)

RowSource: SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Add the following to the criteria row of the CustomerID column in query
design view:

[Forms]![Sales Dialog]![cboCustomer] OR [Forms]![Sales Dialog]![cboCustomer]
IS NULL

By testing for OR <parameter> IS NULL the parameter becomes optional, so if
no customer is selected in the combo box, all customers will be returned.
Before amending the query in this way be sure to make a copy of it under a
different name. You'll find that after you've saved the query, if you open
it again in design view Access will have moved things around and the logic
will be less clear, although it will function in exactly the same way, so if
you find you have a problem and need to make further changes to the query it
may be confusing.

Ken Sheridan
Stafford, England
 
K

kevin

Kevin:

1.  The report's underlying query in the Northwind database has the
following WHERE clause:

WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between
[Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year
Dialog]![EndingDate]));

In query design view the expression is in the criteria row of the
ShippedDate column.

As you see this includes the parameters [Forms]![Sales by Year
Dialog]![ShippedDate] and [Forms]![Sales by Year Dialog]![EndingDate]

If the form is not open when the report is opened it will prompt for these
parameters.  They differ slightly from the ones you've cited, so you've
either changed them in your report or you've not posted them quite correctly
here.

The other two parameters which you are being prompted for, Beginning date
and End date, are not used by the Northwind report, so I can only assume
you've added these yourself.

I'd suggest you do as you are thinking and create a new general purpose
dialog form similar to the Northwind form, calling it Sales Dialog, change
the names of the controls on it to BeginningDate  and and EndingDate, and
change the parameters in the reports query to:

[Forms]![Sales Dialog]![BeginningDate]

And:

[Forms]![Sales Dialog]![EndingDate])

Remove any other Beginning date and End date parameters from the query.

2.  Add a check box chkUnpaid, caption something like 'Unpaid invoices
only:' to the new dialog form and in the report's query put the followingin
the criteria row of the Paid column:

Not [Forms]![Sales Dialog]![chkUnpaid]

3.  To use the same dialog form for multiple reports add separate buttons to
the dialog form, one to open each report, and caption them accordingly.  To
open any report open the dialog form first and open the report from this,
rather than opening the report directly.  It is possible to open the report
first and have the report open the dialog form, but with your present
experience you'll probably find opening the form first and using separate
buttons for each report simpler.  Another approach is to have a list box of
available reports on the dialog form so that you can open one or more reports
simultaneously, but again this is perhaps a little too advanced for you at
present.  We can com back to it if you wish.

4.  An expression to compute the pro rata commission on a partial payment
would be along these lines:

= ([AmmountPaid] / [CommisionableAmount]) * [ CommisionableAmount] *
[CommissionRate]

where CommssionRate is expressed as a fractional value, e.g. 0.1 for 10
percent, 0.25 for 25 percent etc.

5.  To group outstanding debtors use the Switch function in a computed
column in the underlying query, e.g. by entering the following in the 'field'
row of a blank column in the query design grid:

DebtorGroup:Switch(Date() – [DueDate] <= 30, 1, Date() – [DueDate] > 30 And
Date() – [DueDate] <= 60, 2, Date() – [DueDate] > 60, 3)

where DueDate is a column in the table of date/time data type.  You canthen
group the report on DebtorGroup.

6.  To select a single customer add a combo box, cboCustomer, to the dialog
form set up as follows (I'm using the Northwind Customers table as an example
here)

RowSource:     SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;

BoundColum:   1
ColumnCount:  2
ColumnWidths  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one.  The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Add the following to the criteria row of the CustomerID column in query
design view:

[Forms]![Sales Dialog]![cboCustomer] OR [Forms]![Sales Dialog]![cboCustomer]
IS NULL

By testing for OR <parameter> IS NULL the parameter becomes optional, so if
no customer is selected in the combo box, all customers will be returned. 
Before amending the query in this way be sure to make a copy of it under a
different name.  You'll find that after you've saved the query, if you open
it again in design view Access will have moved things around and the logic
will be less clear, although it will function in exactly the same way, soif
you find you have a problem and need to make further changes to the queryit
may be confusing.

Ken Sheridan
Stafford, England



kevin said:
I modified the Northwind example database to track a Accounts
Receivable application.  So far everything is working well.  I needto
make a few modifications that I will get to below.  A problem( not
really but an annoyance) is when I run reports I get the dialog to
enter date twice whenever I run I report that tracks employee
commissions.
First it asks for parameter value for Beginning date, then End date:
then
it asks for the Forms!Sales by Year Dialog!BeginningDate and Forms!
Sales by Year Dialog!EndingDate
I don’t know where these get generated from so I don’t know how to
eliminate one.
I also would like to add a parameter to choose only records that have
a check box field checks for PAID.
(the paid checkbox is on the form and in the table ORDERS.)
I am thinking that it may be easier to create or modify the "Sales by
Year Dialog" form to pass whatever variables I need to ALL reports.
Is there a way to have access use the SALES DIALOG and only the sales
dialog for reporting parameters?
So far the only additional reports required are:
EMPLOYEE COMMISSION
(a report that is generated by product sale - freight based on
commissionable amount that varies between 0 and 50 percent)
This works well now but I have a issue that need addressing.  I have a
field in orders for commissionable amount and percent commission but
the commission amount is calculated in the report and not stored in
the table ORDERS.  This is a problem b/c if a partial payment is made
is messes up the commission.
CUSTOMER AGING
I would like to get the report to group 0-30, 30- 60, > 60 if it isnt
too complicated.  My biggest issue is the check box PAID thing to keep
paid invoices from appearing in the report.
This is the same kind of thing I need for commission paid report .
2 Fields INVOICE PAID and COMMISSION PAID in ORDER TABLE and in ORDERS
form to track info.
I would also like to get this report to run for single customers as
well as all customers.
Please Keep in mind I have only been using ACCESS for about 2 weeks if
you decide to help....... LOL
( I am way over my head)

Kevin- Hide quoted text -

- Show quoted text -

Thanks Ken

It looks like it will take me a while to wrap my head around
this......LOL

I will let you know how I do and come back if I have any problems

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top