sum creditors balance up to a specific date

[anchamal]

i need to create a query to find the balance of creditors with specific dates

example 1/1/17 up to 31/1/17

customer A 1/1/17 €20
2/1/17 €25
31/1/17 €25
01/02/17 €100

what was the balance till 31/01/2017 of customer A?

using offce 2016

 

[arnelgp]

you need to sum the credit and payments:


Select [CustomernNo], Sum(nz(creditField,0) - nz(paymentField,0)) As Balance From yourTable Where [TransactionDate] Between [date start] And [date end] Group By [CustomerNo];

 

[isladogs]

Arnels code is fine but your dates are not.
You will have problems if you mix formats like 31/1/17 and 31/01/2017

Make sure your dates are all in the same format e.g dd/mm/yyyy before you run arnel's code

See this other recent post with the same issue
https://www.access-programmers.co.uk/forums/showthread.php?t=297398

 

[plog]

If you are able to use Arn's code then you have larger issues than the one you have presented. Arn's code supposes you haven't structured your data correctly.

Credits and debits should be in the same field ([Amount]) so that you are able to do this to get the balance:

Balance: SUM([Amount])

 

[jdraw]

We shouldn't be guessing at your table structure. Please describe the fields.

What does this really mean?

customer A 1/1/17 €20
2/1/17 €25
31/1/17 €25
01/02/17 €100