By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

Update query, dcount, two tables

P: 32
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).

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",tblPaydayD ates.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.
Oct 4 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,409
Firstly, an UPDATE query can only work if the underlying query is updatable.
A SELECT query including a DCount() call would NOT be so.
You need to look at an UPDATE query that progressively increments the count for every valid date it comes across. This means that an individual record may be updated many times, but that's the way to get it to work.
Oct 4 '07 #2

P: 32
Thanks for the reply NeoPa, but it's working now. For future searchers, I'll post the entire query to show the dcount statement in context:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblFutureTransactions SET tblFutureTransactions.NumberOfPaydaysDuringSavingPeriod = DCount("*","tblpaydaydates","upcomingpaydaydates Between #" & Format(StartSaving, "mm\/dd\/yyyy") & "# And #" & Format(EndSaving, "mm\/dd\/yyyy") & "#");
Credit for the solution goes to Ken Snell at the MS Community boards for fixing the dcount syntax.
Oct 5 '07 #3

NeoPa
Expert Mod 15k+
P: 31,409
OOoops.
Sorry about that. I'll have to review some of mine in that new light then. Thanks for this.
Oct 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.