Show data in every day of months

[armanbkb]

Dear Experts,
Wish you are well.

I have a table as following:
trn_date
amount

data as:
1/1/16-1000
1/1/16-350
5/1/16-5000
18/1/16-6000
18/1/16-(-10000)
20/1/16-500
31/1/16-250

and I need report as following till 20/1/16

1/1/1-1350
2/1/16-1350
3/1/16-1350
4/1/16-1350
5/1/16-6350
6/1/16-6350
7/1/16-6350
8/1/16-6350
9/1/16-6350
10/1/16-6350
11/1/16-6350
12/1/16-6350
13/1/16-6350
14/1/16-6350
15/1/16-6350
16/1/16-6350
17/1/16-6350
18/1/16-2350
19/1/16-2350
20/1/16-2850


what is the process?

 

[sneuberg]

First you will need some source for the days of the month. I'm fairly certain they have to be in a table, i.e., not generated in an expression in a query. In the attached database I fill a table (tblDate) with the days in Jan 2016 with a For -Next loop. Please note that the dates are in American format as that's the way my system settings are.

With those dates in a table you can generate the output you want with the follow query:

SELECT tblDates.CalendarDate, tblStuff.amount, DSum("[amount]","[qryCumlativeAmounts]","[CalendarDate] <= #" & [CalendarDate] & "#") AS Cumlative
FROM tblDates LEFT JOIN tblStuff ON tblDates.CalendarDate = tblStuff.tm_date;

This might make more sense to you if you open the attached database and view it Design View. First note the outer join between the tables. This produces an output with all of the months' days such that the amounts appear on the date specified in the tblStuff table. The tblStuff being the table that contains the data that you specified as input data.

The cumulative amount is formed by the following expression

Cumlative: DSum("[amount]","[qryCumlativeAmounts]","[CalendarDate] <= #" & [CalendarDate] & "#")

At any line of output from the query this sums up the amounts for the dates less to or equal to the date of that line.

The report in this database is just what Access generates from the query. I didn't clean it up.

 

[armanbkb]

thnx but help me little bit more.
why there is no result?

 

[sneuberg]

Maybe because of your date settings. In the revised attached version I've put this function below from Allen Browne's Site in a module and then use it in the expression in the query.

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

This is the revised query with this function to convert the dates to American format.

SELECT tblDates.CalendarDate, tblStuff.amount, DSum("[amount]","[qryCumlativeAmounts]","[CalendarDate] <= " & SQLDate([CalendarDate])) AS Cumlative
FROM tblDates LEFT JOIN tblStuff ON tblDates.CalendarDate = tblStuff.tm_date;

Please give this version a try.

 

[armanbkb]

Thnx a lot
just help me little bit more

I need 1 report for per day. here 2 report shown in one day. I know I have 2 data in one day but i need to show 1 data per day with last balance

 

[arnelgp]

how do you define what you need, one report per day?

 

[sneuberg]

not solved

Sorry but I need to get some sleep now and then.

This was easy to fix. I just took the output of the query and used that in a second aggregate query to combine the like rows. You could also do this in a second query that would select distinct values of the date and amount.

 

[arnelgp]

the function SQLDate() received a single variant parameter.
so it can accept a valid string date.

using string date as parameter i get the result:

?SQLDate("1/1/2016")
result: "#01/01/2016 00:00:00#"
desired: "01/01/2016#", since there is no time value on the passed string.

the fix, cast the passed parameter to string, then back to date.

If IsDate(varDate) Then
varDate = varDate & ""
If DateValue(CDate(varDate)) = CDate(varDate) Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If

 

[sneuberg]

the function SQLDate() received a single variant parameter.
so it can accept a valid string date.

using string date as parameter i get the result:

?SQLDate("1/1/2016")
result: "#01/01/2016 00:00:00#"
desired: "01/01/2016#", since there is no time value on the passed string.

I see that your solution fixes that problem, but is it a problem in this case?
Aren't "#01/01/2016 00:00:00#" and "01/01/2016#" the same so far a date type is concerned? What problem is this causing in the database I uploaded? How is the output wrong?

 

[arnelgp]

hello mr.sneuberg, there is nothing wrong with your code.
in fact, it is doing what it should do.

just making a test on the function.
i'm just pointing out that i passed a string date without time, so the expected result is a string without time part.

 

[sneuberg]

@ Mr Argnelgp

Maybe this should be fixed on the web site where I got it. I noticed that the Home Page has Allen Browne's email address if you want to suggest this to him.