Count how many days since and on what date

[alcapps]

how much automation are you wanting with this process?
User to press a button to check for missing scheduled transactions?
for all banks or one bank at a time.


you can always fill a list box with all the missing dates for each bank, as the user selects the item pull up a box with allows the user to fill in the missing items using the update query. Then refresh the list box. Until there are no more items.
Again Not sure what you are really trying to accomplish.

in other words..
list box on the left with missing dates in order and banks. as the user selects them you would have blank unbound boxes on the right that would be updated by the user and a button to update the database. in that button once the update process is finished it would blank out the text boxes on the right and refresh the list box on the left.

hope this helps

[burrina]

Hey, that sounds like a plan to me. Never thought of it quite like that. If I click my heel 3 times, will it happen?

[alcapps]

only if you name is Dorothy. not sure about your comment. What I suggested is not that difficult.

[burrina]

Yes, well you don't understand the db is why. I appreciate your trying to help and yes, a listbox is quite simple, what I am trying to do is NOT. I don't even have the data requirements to fill that box yet. If you think you have the solution though, i am all ears.

Thanks,

[alcapps]

To fill the list box.. create a query to group the banks

vba..

recordset1 list of banks (query of unique banks from transaction table)
recordset2 list of transactions (order by date)
recordset3 holds the date bank and dates that are missing
if you use a table for recrodset3 remove the records before starting.

loop thru banks


movefirst recordset2 transaction records
tempdate = date
movenext recordset2 transaction records
loop thru transactions for that selected bank
if date <> tempdate + 1
write record to recorset3 or do an listbox add row.
recordset1 bank and tempdate + 1 for date.
tempdate = date + 1
loop until date = date + 1
else
Tempdate = date
end if
movenext recordset2
end loop on transactions

movenext recordset1
end loop on banks

at the end you have the list of banks and bad dates.

if you have repeat offenders for dates then you can create a table to check to see if you want to ignore them.

[burrina]

Do you have my db? If so try implementing that and see what happens!

[alcapps]

ok, where is your event , transactions details?
if you had that what I laid out for you would work.

so for and event 21
you have transactions of checks with the date they were done and the amounts.
frequency is not an issue. you just add d,wkdays,wkly,number of days, m, q, y.
put that as my + 1 for each day like you were looking for to the frequency. pass that as a param to your vba code and you should be fine.

and then click your heels three times , there is no place like home..

[burrina]

All of those records in the tblEvent are daily events and should have been entered each and every day. From their original start date which was EventStart onward.
The only other details that are important to keep together is; Same Bank, Same Payee, Same Amount,
You dont realize this, but frequency is an issue: Other criteria is in play here but I do NOT want to get into that, it is NOT needed for this scenario. I will attempt to re-explain my end result.
Need list of all dates where PeriodTypeID = "d" and RecurCount = 0 or 1 and dteentered = between EventStart and Date()
and dteenterd IsEmpty, i.e. Has NO Date.
In other words ALL events that were originally scheduled and have NOT been entered when they were suposed to be.
List of the days they WERE supposed to be entered on.

PeriodTypeID = "d" means it is a daily event, it should be entered each and evry day.
RecurCount = 0 means that it has NO expiration date.
RecurCount = 1 means that it is a ONE time event.
EventID = PK
Bank = Name of Bank
Payee = Person who event was scheduled for.
Debit = amount to deduct from bank.
FrequencyAmnt = the same as debit.
Credit = amount to add to bank account, i.e. deposit.
EventStart = original date event was entered.
nextschdte = next date event is scheduled to be entered on.
dteentered = last date event was entered on.

Look at tblEvent, all of those are daily events that should be entered every day from the EventStart date.
EventID 21 1/4/2013 for bank Fly By Night for Billy Ocean for $100.00 was scheduled to be entered again on 1/5/2013
it was not and was not entered ever since then except on 1/10/2013 and dteentered shows this. Your vba skills are obviously better than mine, but your grasp of what I need is not. Again, I still have NOT got my end result, much less moving on from there.
Thanks,

[alcapps]

newtable.mdb

I am giving you the quick version .. the output goes to a table and you can bind that to list box or combo box.

in module 1
sub function can be run to fill the table. missingTrans

take a look and let me know if we are not on the right track here.

[burrina]

Ok, I just downloaded it, looks good. I will beta test and let you know.

Thanks,

[alcapps]

sorry this wasn't for you.. burrina

[burrina]

Then who was it for? My table and db!!!

[alcapps]

oh.. sorry. I copied another db there by mistake .. I deleted before you could see it.

in your database make sure you have the dao 3.6 library selected in references.

or the code I gave you won't work in your original database.

Hope that worked for you.

[alcapps]

yeah if that solution works for you could check to see the bank and date exists in the table and keep it a rolling table instead of a clean and input new.

so if someone said they didn't care about a single or set of dates you could leave them in the table and add one more field as to ignore True, False. and you could change the code to not add that date again. Anyway that is a good base you can tweak it to your needs.

just remember that the table is cleared out in the code I gave you. You could skip that part if you needed to retain dates that you already know.