sum creditors balance up to a specific date (1 Viewer)

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

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];

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

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])

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

Click to expand...

Never format a date UNLESS you want to turn it into a string. ALWAYS use date data types with no format property. make sure to set unbound controls to use the "Short Date" format. That tells Access that you want the control to be a "date". When controls are bound to date data type fields, Access knows the control should be a date so there is no need to set the format property. This allows Access to use your Windows date format. The only time you will ever have to worry about a date format is if you are creating an SQL string with a date parameter. SQL REQUIRES date STRINGS to be US date format.

Formatting dates is what leads to all the problems people have with dates.

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