Dsum in report header problem

[Guest]

I have a text box in a report header where I need to use dsum because the I
need the sum of fields in a table that is not the recordsource of the report.
I've hit errors or undesired results no matter what I've tried.

From code (rpt header format), if the first record in the table matches the
criteria, it returns the sum of all records. If the first record doesn't
match the criteria, it returns nothing.
Me.Text127.ControlSource =
"=DSum('[CostPerPaycheck]','tblSavingsFunds',[fundstatus] = 'Waiting')"

From code (rpt header format), when attempting to print prevew, this gives
error 2434, "the expression you entered contains invalid syntax" (added '
around criteria)
Me.Text127.ControlSource =
"=DSum('[CostPerPaycheck]','tblSavingsFunds','[fundstatus] = 'waiting'')"

This works when entered directly into the textbox:
=DSum("[CostPerPaycheck]","tblSavingsFunds","[fundstatus] = 'waiting'")
but I can't get it to work with another necessary criteria that contains a
date variable (the variable works perfectly behind the form in some
"me.textbox.controlsource=" statements)

When keyed directly into the text box and combined with the date variable
(nextpayday) this returns #Error
=DSum("[CostPerPaycheck]","tblSavingsFunds","[fundstatus] = 'waiting' and
[savingsperiodenddate] > nextpayday")

When keyed directly into the text box, these automatically add brackets
around nextpayday and then prompt for a value for nextpayday when attempting
print preview;
=DSum("[CostPerPaycheck]","tblSavingsFunds","[fundstatus] = 'waiting' and
[savingsperiodenddate] > #" & [nextpayday] & "#")
=DSum("[CostPerPaycheck]","tblSavingsFunds","[fundstatus] = 'waiting' and #"
& [savingsperiodenddate] & "# > #" & [nextpayday] & "#")

I'm stumped. I've searched an can't find anything. All help is
appreciated. Thank you.

 

[Guest]

It isn't clear what values/variables are coming from where.
I assume CostPerPaycheck and FundStatus are fields in tblSavingsFunds. Where
do you expect SavingsPeriodEndDate and NextPayDay to come from (table, record
source of report, control name,...)?

 

[Guest]

Hi Duane. Thanks for your help.
Yes, CosPerPaycheck and FundStatus are fields in the table that is the
recordsource of the report. SavingsPeriodEndDate is also a field in that
table.
NextPayday is a variable that is being set both in report_open and
reportheader _format.

 

[Guest]

Duane,
I made a mistake in my previous post. Yes, CostPerPaycheck and FundStatus
are fields but they are NOT fields in the report's recordsource.
SavingsPeriodEndDate is also a field, but is also NOT in the report's
recordsource. Sorry about that.

 

[Guest]

So, nextpayday is not a field in a report or in tblSavingsFunds? If this is
the case then I would either get it into the record source or domain of the
DSum() or a function that returns the value.

 

[Guest]

First, I assume you saw my previous post correcting my answer to your
question asking if the fields in the dsum were in the report's recordsource.
They are not.

As for putting nextpayday into the domain specified in the dsum, I wouldn't
know how to do that. Establishing nextpayday takes these 5 lines of code:

FirstPayday = #7/4/2007#
NumberOfDaysSince = DateDiff("d", FirstPayday, date)
modulus = NumberOfDaysSince Mod 14
DaysToNext = 14 - modulus
nextpayday = DateAdd("d", DaysToNext, date)

and these are after the 5 lines to declare the variables used in the process.

I just tried making nextpayday the controlsource of a new text box (text138)
and then used text138 in place of nextpayday in the dsum entered directly
into the textbox. Like this:
=DSum("[CostPerPaycheck]","tblSavingsFunds","[fundstatus] = 'waiting' and #"
& [savingsperiodenddate] & "# > #" & [text138] & "#")
Now it does not prompt for a value for nextpayday, but it ignored the date
comparison criteria completely. The textbox shows nextpayday correctly, but
manipulating SavingsPeriodEndDate in the table had no impact on the results
in the dsum textbox. Only manipulating the contents of the field FundStatus
affected the results.

 

[Guest]

Duane,
I just tried putting the code that finds nextpayday into a module and
calling it from behind the report, but I couldn't make that work either. In
fact no where on the report understood nextpayday after that. The most
likely reason it broke everything is that I don't know how to make something
like that work from a module, I was just giving it my best shot.