ACCESS FEED: RE: [MS_AccessPros] Help with my report

Kamis, 24 Juli 2014

RE: [MS_AccessPros] Help with my report

Never mind, I figured it out. I changed the field format to 'Currency' and now it is working.

Thanks again for your help.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <sarahk@...> wrote :

Duane,

Thank you so, so much. I got the sorting and the running balance.

I have just one more thing: sometimes the running balance prints a 'funny' number instead of zero (I lost the column alignment with copy/paste, so it does not look so clear):

G/L Date Invoice Date Service Date Trans Date Prv Insurance Ty Invoice No Debit Credit Balance

5/14/2014 10/15/2013 4/9/2013 5/14/2014 HAM I 1084 81.28 81.28

5/14/2014 10/15/2013 4/9/2013 5/15/2014 P 1084 80.76 0.5199999999

5/14/2014 10/15/2013 4/9/2013 5/15/2014 W 1084 0.52 -3.996802888

5/14/2014 10/15/2013 4/9/2013 5/14/2014 HAM I 1085 43.11 43.11

5/14/2014 10/15/2013 4/9/2013 5/15/2014 P 1085 43.11 -7.105427357

the -3.996802888 and -7.105427357 should be 0. The third line (0.5199999) shows as .052 on the report,

only when I export to Word it changes to this number.

This how I defined the data source for balance:=nz([DEBIT])-nz([CREDIT]).

What am I doing wrong?. I tried decimal places 2 and decimal places auto, but it did not make a difference. Debit, Credit and Balance are text boxes.

What am I doing wrong?

As always all help is greatly appreciated.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

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

__._,_.___

Posted by: sarahk@schemesoftware.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)