H
hom370
I am trying to add a functionality into a db I use for my checkbook that will
help me plan for and save money for future large expenses. Into a table
called tblFutureTransactions I want to enter records for upcoming large
expenses, the dollar amount needed, the date the money is needed, and the
date I want to start saving. Then I want the db to automatically figure and
deduct from my balance an amount of money from each paycheck to save for the
future expense. To do that I need to be able to find how many paydays fall
within those dates (StartSaving and EndSaving). This is what I have so far:
I have a table of future expenses:
tblFutureTransactions it has these fields:
StartSaving (date/time)
EndSaving (date/time)
NumberOfPaydaysDuringSavingPeriod (number)
And a table of the dates of my paydays (every other Wednesday) for the next
year:
tblPaydayDates it has only two fields
PaydaysPriKey (primary key auto-numbered)
UpcomingPaydayDates (date/time)
So I am trying to run an update query where the "update to" field is a
dcount statement that counts records in tblPaydayDates using date range
criteria from tblFutureTransactions. The dcount statement doesn't work, but
you can probably see what I'm trying to do:
DCount("PaydaysPriKey","tblPaydayDates",tblPaydayDates.UpcomingPaydayDates
Between tblFutureTransactions.StartSaving And tblFutureTransactions.EndSaving)
I'm trying to use the update query (and the dcount inside it) to update
tblFutureTransactions.NumberOfPaydaysDuringSavingP eriod with the count of
how many of the records in tblPaydayDates fall between
tblFutureTransactions.StartDate and tblFutureTransactions.EndDate.
Thanks for your help!
help me plan for and save money for future large expenses. Into a table
called tblFutureTransactions I want to enter records for upcoming large
expenses, the dollar amount needed, the date the money is needed, and the
date I want to start saving. Then I want the db to automatically figure and
deduct from my balance an amount of money from each paycheck to save for the
future expense. To do that I need to be able to find how many paydays fall
within those dates (StartSaving and EndSaving). This is what I have so far:
I have a table of future expenses:
tblFutureTransactions it has these fields:
StartSaving (date/time)
EndSaving (date/time)
NumberOfPaydaysDuringSavingPeriod (number)
And a table of the dates of my paydays (every other Wednesday) for the next
year:
tblPaydayDates it has only two fields
PaydaysPriKey (primary key auto-numbered)
UpcomingPaydayDates (date/time)
So I am trying to run an update query where the "update to" field is a
dcount statement that counts records in tblPaydayDates using date range
criteria from tblFutureTransactions. The dcount statement doesn't work, but
you can probably see what I'm trying to do:
DCount("PaydaysPriKey","tblPaydayDates",tblPaydayDates.UpcomingPaydayDates
Between tblFutureTransactions.StartSaving And tblFutureTransactions.EndSaving)
I'm trying to use the update query (and the dcount inside it) to update
tblFutureTransactions.NumberOfPaydaysDuringSavingP eriod with the count of
how many of the records in tblPaydayDates fall between
tblFutureTransactions.StartDate and tblFutureTransactions.EndDate.
Thanks for your help!