DSum Criteria

  • Thread starter Thread starter michael c
  • Start date Start date
M

michael c

I have a query that pulls from a table called tblAccounts.
The table fields are: Account, ValidFrom, ValidTo. There
are about 80 accounts listed and the table looks like this:

Account ValidFrom ValidTo
123345 1/15/03 12/31/03
124563 5/23/03 12/31/03

My query counts the number of working days:
1. between the first of this year and ValidFrom
2. between ValidFrom and ValidTo

So I have a query that looks like this (the math is wrong
on the working days here; it's just an example):

Account ValidFromDays ValidToDays
123345 45 50
124563 34 45

The problem is that I need to subtract out holidays from
both of those numbers. So, I have another table,
tblWorkingDaysAdjustments, in my db that lists holidays.

Adjustment AdjustmentDate
1 12/25/03

My query, which has tblAccounts as its record source, is
trying to lookup the tblWorkingDaysAdjustments table and
count the number of adjustment days for each Account in
the query by using this expression:

CurrentAdjustmentDays: DSum
("[Adjustment]","[tblWorkingDaysAdjustments]","[AdjustmentD
ate] Between [ValidFrom] And [ValidTo]")

The problem is that I can't get the expression to read the
ValidFrom and ValidTo fields in my query's record source.
I tried using Query![ValidFrom] for example, but no dice.
Any thoughts would be great. Thanks!
 
Try:
CurrentAdjustmentDays: DSum("[Adjustment]",
"[tblWorkingDaysAdjustments]",
"[AdjustmentDate] Between #" & [ValidFrom] & "# And #" & [ValidTo] &
"#")
 
Back
Top