Update query and using two tables in dcount statement

[Guest]

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!

 

[Ken Snell \(MVP\)]

Change the DCount function to this:

DCount("*","tblPaydayDates","tblPaydayDates.UpcomingPaydayDates
Between tblFutureTransactions.StartSaving And
tblFutureTransactions.EndSaving")

 

[Guest]

Ken,
Thanks for your reply, but I got strange results. When I changed the dcount
statement and ran the query, the query didn't seem to do anything at all. It
didn't even give me the notice of how it was going to update x rows. (x
being 0 if no records met the criteria to be updated or x being an actual
number of records that did meet the criteria and would be updated). Usually
it has been giving those notices, even when the query didn't give the results
I am trying for.

 

[Ken Snell \(MVP\)]

Post the SQL statement of the query -- let's see what is happening. And tell
us how you're running the query -- if by macro or VBA code, post the
steps/code.

 

[Guest]

Here is the query:
UPDATE tblFutureTransactions SET
tblFutureTransactions.NumberOfPaydaysDuringSavingPeriod =
DCount("*","tblpaydaydates","tblpaydaydates.upcomingpaydaydates Between
tblFutureTransactions.StartSaving And tblFutureTransactions.EndSaving");
I had tentatively planned on called this query (and the other(s) necessary
to complete the calculations on the future transaction records) when the db
opens. I've been testing this by launching it manually from the database
query section, and then going and looking to see what it did to the records
in the tblFutureTransactions table.
Thanks again.

 

[Ken Snell \(MVP\)]

OK - My error in not noticing it the first time I replied. You cannot use
DCount to look at one table while trying to use another table's fields in
the criterion argument unless you concatentate the values into the string.
You're looking at tblpaydaydates and wanting to use fields from
tblFutureTransactions table for filtering.

But before we get into how to make your feature work, we need to relook at
your table structure.

Your tblFutureTransactions table has just three fields, none of which
appears to be a primary key. In order for an update query to work
eventually, this table needs to have a primary key field. You'll need to add
one -- probably best to use an Autonumber field as the primary key field.
Note that you cannot add an Autonumber field to a table after it already has
data in it, so you may need to build a new table and delete the old one.

After you have added a primary key field to tblFutureTransactions, now you
should be able to use an update query similar to this:

UPDATE tblFutureTransactions SET
tblFutureTransactions.NumberOfPaydaysDuringSavingPeriod =
DCount("*","tblpaydaydates",
"upcomingpaydaydates Between #" &
Format(StartSaving, "mm\/dd\/yyyy") &
"# And #" &
Format(EndSaving, "mm\/dd\/yyyy") & "#");

 

[Guest]

Very cool. Works great. Thank you!

(by the way I did already have a primary key field - auto numbered - in
that table I just failed to mention it.)

One more question about something you said. You said, "You cannot use
DCount to look at one table while trying to use another table's fields in
the criterion argument unless you concatenate the values into the string."
I just don't understand exactly what you did or why it works but mine didn't?
Possible answer to second part: "it's just the way it is"? or is there an
understandable reason?

 

[Ken Snell \(MVP\)]

In your first example, you did not create any type of string for the third
argument of the DCount function -- you just listed the desired "WHERE"
clause without any " characters to show it as a string. Therefore, ACCESS
did not know what to do with it.

In my first example, I included everything inside a string, which then
failed because the string contained references to a table that was not in
the original table (second argument).

In my last example, I built a string by using text (inside " characters) and
concatenating the actual values of the fields into the string:

"upcomingpaydaydates Between #" &
Format(StartSaving, "mm\/dd\/yyyy") &
"# And #" &
Format(EndSaving, "mm\/dd\/yyyy") & "#"

The & operator is used to concatenate different string parts together into
one new, longer string. Note that I put the StartSaving and EndSaving fields
outside the " characters, and I formatted them to the desired mm/dd/yyyy
format so that Jet sees the dates as US formatted. The # delimiter is used
to delimit date values. So, let's assume that the value of StartSaving was
January 1, 2007, and the value of EndSaving was March 25, 2008. The
resulting string that would be built in the query would be this:

"upcomingpaydaydates Between #01/01/2007# And #03/25/2008#"

 

[Guest]

Gotcha. I'll try to remember this, and just in case I've bookmarked this
page for future reference in case I need to brush up later. Thanks a lot for
your time and your help.

 

[John Spencer]

Ken,

Pardon me, but you can ADD an autonumber to a table at any time. I think
what you meant is that you can't convert (change) a field to an autonumber
field if the table already has data A quibble perhaps, but in this case the
user could add an autonumber field and use that as the primary key.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Snell \(MVP\)]

Ken,

Pardon me, but you can ADD an autonumber to a table at any time.

I stand corrected. I really need to remember that I'm getting old, and
memory sometimes is a bit unreliable. Thanks.

I think what you meant is that you can't convert (change) a field to an
autonumber field if the table already has data

Yes, that is what I was thinking.

 

[Guest]

Thanks a lot you guys!
Have a good day!