Allen Browne has code provided by Andy Baron at http://www.allenbrowne.com/ser-33.html that describes how to use code to change the sorting of a report at runtime. I have often used another method without code. Assuming you have an option group [optSortBy] on your form with values of 1,2,3 for G/L, Invoice, and Transaction Dates.
In your query, create a column using an expression like:
SortBy: DateValue(Choose(Forms!YourFormName!optSortBy, [GLDateFieldName], [InvoiceDateFieldName], [TransactionDateFieldName]))
Then in your report, I assume you first sort by customer so you can force a new page after. Then sort by [SortBy] which will reflect the date selected from the form.
You can use the Running Sum property "Over Group".
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 23 Jul 2014 17:46:59 -0700
Subject: [MS_AccessPros] Help with my report
I am creating a new report, which is actually a Ledger by Customer. I start the report from a form. The form provides a choice on how to sort the report by G/L Date/by Invoice Date/by Transaction Date.
The data is provided to the report by a query.
I have the following questions:
1) Can I specify in the query to sort the data based on the choice made in the form. eg. by G/L Date, by Invoice Date or by Transaction Date. or do I need separate queries for each sorting option?. (Most probably there is an IIF statement , but I dont know how to use it.)
2) I need to start a new page for each Customer. If the Customer number changes, I need to start a new page.
3) I print a debit and credit column on the report. I need to add a running subtotal by customer.
eg
DEBIT CREDIT BALANCE
65.00 65.00
10.00 55.00
a) how do I calculate the balance based on the debit, credit fields in the report..
b) the balance needs to be reset to 0 when the customer number changes.
As always all help is greatly appreciated.
Thanks
Sarah
In your query, create a column using an expression like:
SortBy: DateValue(Choose(Forms!YourFormName!optSortBy, [GLDateFieldName], [InvoiceDateFieldName], [TransactionDateFieldName]))
Then in your report, I assume you first sort by customer so you can force a new page after. Then sort by [SortBy] which will reflect the date selected from the form.
You can use the Running Sum property "Over Group".
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 23 Jul 2014 17:46:59 -0700
Subject: [MS_AccessPros] Help with my report
I am creating a new report, which is actually a Ledger by Customer. I start the report from a form. The form provides a choice on how to sort the report by G/L Date/by Invoice Date/by Transaction Date.
The data is provided to the report by a query.
I have the following questions:
1) Can I specify in the query to sort the data based on the choice made in the form. eg. by G/L Date, by Invoice Date or by Transaction Date. or do I need separate queries for each sorting option?. (Most probably there is an IIF statement , but I dont know how to use it.)
2) I need to start a new page for each Customer. If the Customer number changes, I need to start a new page.
3) I print a debit and credit column on the report. I need to add a running subtotal by customer.
eg
DEBIT CREDIT BALANCE
65.00 65.00
10.00 55.00
a) how do I calculate the balance based on the debit, credit fields in the report..
b) the balance needs to be reset to 0 when the customer number changes.
As always all help is greatly appreciated.
Thanks
Sarah
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Yahoo Groups
Improved Group Homepage!
The About page of your Group now gives you a heads up display of recent activity, including the latest photos and files
Yahoo Groups
Control your view and sort preferences per Yahoo Group
You can now control your default Sort & View Preferences for Conversations, Photos and Files in the membership settings page.
.
__,_._,___
Tidak ada komentar:
Posting Komentar