Opening Balance in Report , www.muhammad.aamir.net.tc , in Download u got JounalVoucher.mdb

Question

1.00/5 (3 votes)

See more:

If you don't understand what I say, please download and resolve.
In Report, I have issue with Opening Balance

Query is...

SQL

SELECT [5 VoucherDetail].vDate, [4 sub].sub_description, [5 VoucherDetail].Txtnarration, [5 VoucherDetail].Debit, [5 VoucherDetail].Credit, [debit]-[credit] AS Balance, [4 sub].s_code
FROM [4 sub] INNER JOIN [5 VoucherDetail] ON [4 sub].s_code = [5 VoucherDetail].s_code
WHERE ((([5 VoucherDetail].vDate) Between [forms]![frmLedger]![stdate] And [forms]![frmLedger]![enddate]) AND (([4 sub].s_code)=[forms]![frmLedger]![AccId]));

Kindly resolve this,

Here is File Link To Download
http://www.4shared.com/document/khZrBg5E/aamir_JV.html[^]

or
https://sites.google.com/site/ksenow/research-reports/JournalVoucher.mdb?attredirects=0[^]

or

VB

www.muhammad.aamir.net.tc
in Downloads (Journal Voucher.mdb) u Got That

Posted 24-Mar-11 1:13am

Muhammad Aamir SumSum

Updated 25-Mar-11 1:50am

v11

Add a Solution

Comments

Dylan Morley 24-Mar-11 8:03am

What does 'Not working' mean? Does it error? If so, is there an error message?

Or does it just not do anything? Or does it do something, but not what you expected?!

[no name] 25-Mar-11 7:15am

Hello

Dylan Morley 25-Mar-11 7:19am

Hai

[no name] 25-Mar-11 7:48am

www.muhammad.aamir.net.tc
in Downloads (Journal Voucher.mdb) u Got That Plz Resolved This a simple issue is The ledger Report i Define i Want Opening Balance on That its..

Dylan Morley 25-Mar-11 8:08am

OK see my answer

Johnny J. 25-Mar-11 6:16am

I agree with Dylan - please explain what the problem is. If I am to download and fix anything for you, I need to get your address so that I know where to send the consultancy bill...

[no name] 25-Mar-11 6:19am

[DELETED]@gmail.com

[edit]Email deleted: Never post your email address in any forum, unless you really like spam! If anyone replies to you, you will receive an email to let you know - OriginalGriff[/edit]

[no name] 25-Mar-11 7:39am

muhammad.aamir835@gmail.com
I dOn't Care

[no name] 25-Mar-11 7:42am

muhammad.aamir835@gmail.com

[no name] 25-Mar-11 9:03am

Kindly Join us in this Discussion

Johnny J. 25-Mar-11 9:07am

Sorry, I tried to make it clear to you in a humerous way that I (and most of the other CP) members are NOT going to download anything. My time is too precious for that (to me) - If you want help, you can post the code that's giving you the problems. Also, I don't know you and that means that downloading anything from you is a security risk to me. That is simply not the way the CodeProject works.

[no name] 25-Mar-11 9:15am

There is no Security Problem go to my Site www.Muhammad.aamir.net.tc , In Download Option , Journal Voucher.mdb , my Risponsibllity for this and u see this Section lot of Comments Counter Question Answers But No Body Can Be Answer This

[no name] 25-Mar-11 7:01am

Solution no 1 is not a solution then give Answer in Quickly Mode

[no name] 25-Mar-11 7:45am

www.muhammad.aamir.net.tc
Downloads and Journal Voucher u Got That

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Accepted Answer · 2011-03-25T02:08:00

Solution 2

A few issues here.

1) Your balance is wrong. Within the report details, you're just subtracting [debit]-[credit]. You need to create a running balance.

calculate-a-running-sum-cumulative-total[^]

That's easy in Access 2007, just put a text box on the report and set it's 'RunningSum' property

2) In the 'Opening Balance' DSUM, replace with the following

=DSum("[debit]-(credit)","5 VoucherDetail","[vnum] = " & [Forms]![frmLedger]![Accid] & " AND [vDate] <= #" & [Forms]![frmLedger]![stdate] & "#")

Posted 25-Mar-11 2:08am

Dylan Morley

Updated 25-Mar-11 3:38am

v3

Comments

[no name] 25-Mar-11 8:11am

Iam Trying That u Said

[no name] 25-Mar-11 8:15am

But Sir I Still UnClear that Plz u Have My Attachment Resolve This and send to Muhammad.aamir835@gmail.com it may help to me Better understand Plz Sir

[no name] 25-Mar-11 8:38am

=DSum("[debit]-(credit)","Ledger") not working i try

[no name] 25-Mar-11 8:46am

Report is Joint With Frmledger Form in frmledger Form Two Ubound Text for STDate And EndinDate and one ComboBox For AccountType = accid , So give That parameter Than My Report Open and Than The Opening Balance Displayed.. Consider

Dylan Morley 25-Mar-11 8:52am

Yes it is, try it for Account Type 'Cash in hand'

* Open form frmLedger.
* Set starting date 01-Jan-11, ending Date 23-Feb-11
* Set account type 'Cash In Hand'

Now open your report. The balance will be -95,500

[no name] 25-Mar-11 8:57am

I Set starting date 02-Jan-11, ending Date 23-Feb-11
1-jan-11 Cash in hand 10000/= Not shown in Opeing Balance

Dylan Morley 25-Mar-11 9:40am

Sorry, my bad - I see what you were doing now. My solution would've given you 'Ending Balance' rather than 'Starting Balance'

See my updated answer, you were close - you need to use # around dates in Access.

[no name] 26-Mar-11 0:29am

This Solution is good but not working
=DSum("[debit]-(credit)","5 VoucherDetail","[vnum] = " & [Forms]![frmLedger]![Accid] & " AND [vDate] <= #" & [Forms]![frmLedger]![stdate] & "#")

[no name] 26-Mar-11 0:57am

U Have a File u Try

[no name] 26-Mar-11 4:28am

in Ledger Report i use for Opening Balance

=DSum("[debit]-[credit]","5 voucherdetail","[s_code] = " & [Forms]![frmLedger]![Accid] & " AND [vDate] <= #" & [Forms]![frmLedger]![stdate]-1 & "#")

This Code is Working But Opening Balance Some time Show Correct and Some Incorrct

For Example

Date is 01/01/2011 Debit is 10000
02/01/2011 Credit is 5000
03/01/2011 credit is 10000 Correct Balance is -5000

When i open Report From 02/01/2011 To 2012

The Opeing Balance Should Be 5000 but they Show -5000 ending Balance of All

-5000 Balance is 3/01/2011 Balance

[no name] 26-Mar-11 4:32am

in Ledger Report i use for Opening Balance

=DSum("[debit]-[credit]","5 voucherdetail","[s_code] = " & [Forms]![frmLedger]![Accid] & " AND [vDate] <= #" & [Forms]![frmLedger]![stdate]-1 & "#")

This Code is Working But Opening Balance Some time Show Correct and Some Incorrct

For Example

Date is 01/01/2011 Debit is 10000
02/01/2011 Credit is 5000
03/01/2011 credit is 10000 Correct Balance is -5000

When i open Report From 02/01/2011 To 2012

The Opeing Balance Should Be 5000 but they Show -5000 ending Balance of All

-5000 Balance is 3/01/2011 Balance

And Kindly Send me a Test Mail on muhammad.aamir835@gmail.com i want ur Email ID

[no name] 26-Mar-11 5:25am

=DSum("[debit]-[credit]","5 voucherdetail","[s_code] = " & [Forms]![frmLedger]![Accid] & " AND [vDate] < #" & Format([Forms]![frmLedger]![stdate],"yyyy-mm-dd") & "#")

This Code My Problem Resolved Thank u for ur Help

m@dhu 25-Mar-11 9:50am

My 5 for your effort.

[no name] 26-Mar-11 0:14am

Hello iam Back

DoStuffZ · Accepted Answer · 2011-03-25T00:57:00

Solution 1

Are you looking for some with case

CASE WHEN "something" THEN [debit]-[credit] END AS Balance,

MSDN[^]

*thrown a wild guess

Posted 25-Mar-11 0:57am

DoStuffZ

Updated 25-Mar-11 0:58am

v2

Comments

[no name] 25-Mar-11 6:59am

Not The Solution u Answer that

Ankur^\m/ 25-Mar-11 7:03am

Then why have you accepted the solution?

[no name] 25-Mar-11 7:07am

Yar App Dekho to sahi Sab Log Comment Pass kar rahey Hain App bhi Comment's dey detey

Ankur^\m/ 25-Mar-11 7:23am

[Hindi]Bina logic samjhe isko solve karna muskil hai. Ish query ko humlog run kar ke bhi nahi dekh sakte. aur toh aur 4shared most logo ke office mein block rehta hai. tumne problem bhi detail mein mention nahi kiya hai. ish tarah ki query ko part part kar ke solve kate hai. koshish karo aur agar nahi hota hai toh niche diye link par post karo. Hum aapko yaha acche se post karne me madad karenge
http://www.codeproject.com/Forums/1580229/General-Indian-Topics.aspx
[/Hindi]
English Translation: It's difficult to solve this without understanding the logic. We cannot even run this query. Moreover 4shared is blocked for most of us at work. You haven't also mentioned the problem in detail.
Bigger query are made/solved by dividing it into smaller parts. Try using the technique and if you are still not able to solve it, post it in the below link. We will help you post the question in a better way.

[no name] 25-Mar-11 7:36am

Now Here is the Link Now Donload
https://sites.google.com/site/ksenow/research-reports/JournalVoucher.mdb?attredirects=0

[no name] 25-Mar-11 7:46am

www.muhammad.aamir.net.tc
in Downloads (Journal Voucher.mdb) u Got That

[no name] 25-Mar-11 7:08am

U Must Download and See in Ledger Report Solve This Opening Balance i Required That it not a big issue