Missing Dates - Running Balance

[valglad]

Hello,

I need help on how to deal with identifying missing dates, creating new records and assigning values to them.

The data looks like that:

Account_Number----------Currency-----------PostingDate--------OpeningBalance-----NetDaily---------Closing Balance
1234-------------------------USD--------------July 1, 2016----------$80-----------------+$20-----------------$100
1234-------------------------USD--------------July 3, 2016----------$100----------------+$40-----------------$140
1234-------------------------USD--------------July 4, 2016----------$120----------------+$10-----------------$130
1234-------------------------SGD--------------July 1, 2016----------$160-----------------$5-------------------$155
1234-------------------------SGD--------------July 7, 2016----------$180----------------+$0------------------$180
1235-------------------------USD--------------July 1, 2016----------$100---------------+$10------------------$110
1235-------------------------USD--------------July 3, 2016-----------$10------------------$5-------------------$5

So, the objective is - if a date for the Acc Number/Currency combination is missing - to create a new record (i.e. for 1234-USD for July 2, 2016) and assign the closing balance from July 1 to the opening balance of the newly created record ($100), put zero for the NetDaily and put the sum of the Opening plus NetDaily into closing ($100+$0, or simply $100 since there was no activity that day).

The time gap could be up to two weeks. The data is sent by individual banks in csv format and is uploaded nightly into the database. The banks are located in different countries and operate on their own schedules. If the bank is closed, there is no report.

A query based on the table feeds an excel spreadsheet via pivot table and displays a cash position per day. Since some dates are missing for some combos of (Acc Num, Curr) - the opening balance, Net daily and the closing balance display NULL. To make the report uniform I need the data to have all elements for all days.

I did google different resources and ask some other forums but no results so far. So, if anyone can help - that would be really great.

Thanks
Valeri

[ranman256]

i answered this on the other board. It has code to cycle thru a calendar, adding EVERY date to a table,
then use that table in an outer join to build the missing dates.

[valglad]

Thank you. But my problem is bigger than just identifying the dates. That part is easy, it's assigning values to the new records that I have the problem with, especially if the sequence of the missing dates is more than one.

Btw, in your code, vStart needs a datevalue function around the expression because otherwise it a string.
sub buildDates(pvMo, pvYr)
dim vDate, vStart, vEnd


docmd.setwarning false
vStart = pvMo & "/1/" & pvyr
vEnd = dateAdd("d",-1,dateAdd("m",1,vStart))
vDate = vStart

while vDate < vEnd
sSql = "insert into tMonthDates ([Date]) values (#" & vDate & "#)"
docmd.runSql sSql
vDate = dateAdd("d",1,vDate)
wend
end sub