Dsum as ControlSource for text box on report not working

[ringer]

Hi,
I have a text box on report where I need to have dsum return a total.
The records I need the total from are not in the table that is the report's recordsource, and to complicate things further, I need to use a variable that is derived from behind the report in the criteria for the dsum.

When I try to see print preview, this version of the statement errors before opening saying "Access cannot find the field 'SavingsPeriodEndDate' referred to in your expression":
Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds','[FundsStatus] = 'Waiting' and #" & [nextpayday] & "# <= #" & [SavingsPeriodEndDate] & "#')"

[SavingsPeriodBeginDate]and [FundStatus] are fields in tblSavingsFunds.
nextpayday is a date variable set in the same section of code where this controlsource statement is (report_open).

Thank you

[Rabbit]

Hi,
I have a text box on report where I need to have dsum return a total.
The records I need the total from are not in the table that is the report's recordsource, and to complicate things further, I need to use a variable that is derived from behind the report in the criteria for the dsum.

When I try to see print preview, this version of the statement errors before opening saying "Access cannot find the field 'SavingsPeriodEndDate' referred to in your expression":
Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds','[FundsStatus] = 'Waiting' and #" & [nextpayday] & "# <= #" & [SavingsPeriodEndDate] & "#')"

[SavingsPeriodBeginDate]and [FundStatus] are fields in tblSavingsFunds.
nextpayday is a date variable set in the same section of code where this controlsource statement is (report_open).

Thank you

And where is SavingsPeriodEndDate located?

[ringer]

Sorry, I got mixed up and typed the wrong field name. SavingsPeriodEndDate is also a field in the table tblSavingsFunds.

[Rabbit]

It should be this:
"# <= [SavingsPeriodEndDate])"

[ringer]

I tried your suggestion
and #" & [nextpayday] & "# <= [SavingsPeriodEndDate])"
and
and #" & [nextpayday] & "# <= [SavingsPeriodEndDate]')" -notice the single quote between ] and )

Both give error 2434, invalid syntax.

Then I tried
and #" & [nextpayday] & "# <= [SavingsPeriodEndDate])" and removing the single quote before [FundStatus] but that prompted for a value for FundStatus when I tried to print preview.

[patjones]

Maybe you could try removing the quotes from around the date fields:

' [FundsStatus] = 'Waiting' And #[nextpayday]# <= #[SavingsPeriodEndDate]# '

But it might be more complicated than that. I find that Access doesn't like date comparisons such as [Date1] < [Date2] when one or both of the date values is null, or an empty string. If you have null dates in your table, it might be complaining about that.

[ringer]

Maybe you could try removing the quotes from around the date fields

Thanks for the suggestion Zepphead, but when I tried it errored saying "invalid syntax".

Then I tried removing the [ ] from around the date variable nextpayday like this:

and #" & nextpayday & "# <= #" & [SavingsPeriodEndDate] & "#')"
and this
and #" & nextpayday & "# <= #" & CDate([SavingsPeriodEndDate]) & "#')"

but when attempting print preview, both gave the error "Access can't find the field 'SavingsPeriodEndDate' referred to in your expression".

Then I tried:
and #" & nextpayday & "# <= #" & CDate([tblSavingsFunds].[SavingsPeriodEndDate]) & "#')"
and also
and #" & nextpayday & "# <= #" & [tblSavingsFunds].[SavingsPeriodEndDate] & "#')"

but when trying to print preview, both gave the error:
"Access can't find the field '|' referred to in your expression"

[ringer]

But it might be more complicated than that. I find that Access doesn't like date comparisons such as [Date1] < [Date2] when one or both of the date values is null, or an empty string. If you have null dates in your table, it might be complaining about that.

I've seen that before too and have had to build logic in to intercept that situation when it comes up, but I don't think that's happening here.
I found that for some reason this works:
Me.Text127.ControlSource = and t=DSum('[CostPerPaycheck]','tblSavingsFunds','[SavingsPeriodEndDate] >= #" & nextpayday & "#')"

but it stops working when I add this criteria back into the string:
and [FundStatus] = 'Waiting'

Like this it errors saying invalid syntax when attempting a print preview:
Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds','[SavingsPeriodEndDate] >= #" & nextpayday & "# and [FundsStatus] = 'Waiting'')"

Here I removed the single quotes from the whole criteria statement, and now it returns a total of all CostPerPaycheck fields in the table if the first record has [FundStatus] = 'Waiting', but returns nothing if the first record doesn't:
Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds',[SavingsPeriodEndDate] >= #" & nextpayday & "# and [FundStatus] = 'Waiting')"

[Rabbit]

Ok, I think I see now. Use this:

Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds','[SavingsPeriodEndDate] >= #" & nextpayday & "# and [FundStatus] = ""Waiting""')"
 

[ringer]

Yes! That did it. I've never seen that before. Thanks very much everybody, especially Rabbit!

[ringer]

One other quick question about this. How can I make this statement return 0.00 when nothing matches the criteria? Right now the text box is blank when nothing matches the criteria.

[Rabbit]

Surround that with the Nz function. Nz returns an alternate value if the value you give it is Null.

Nz(DSum(), 0.00)

[ringer]

Thanks very much for your help!

[Rabbit]

Not a problem, good luck.