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 'SavingsPeriodE ndDate' referred to in your expression":
Me.Text127.Cont rolSource = "=DSum('[CostPerPaycheck]','tblSavingsFu nds','[FundsStatus] = 'Waiting' and #" & [nextpayday] & "# <= #" & [SavingsPeriodEn dDate] & "#')"

[SavingsPeriodBe ginDate]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 'SavingsPeriodE ndDate' referred to in your expression":
Me.Text127.Cont rolSource = "=DSum('[CostPerPaycheck]','tblSavingsFu nds','[FundsStatus] = 'Waiting' and #" & [nextpayday] & "# <= #" & [SavingsPeriodEn dDate] & "#')"

[SavingsPeriodBe ginDate]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 SavingsPeriodEn dDate located?

[ringer]

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

[Rabbit]

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

[ringer]

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

Both give error 2434, invalid syntax.

Then I tried
and #" & [nextpayday] & "# <= [SavingsPeriodEn dDate])" 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]# <= #[SavingsPeriodEn dDate]# '

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 & "# <= #" & [SavingsPeriodEn dDate] & "#')"
and this
and #" & nextpayday & "# <= #" & CDate([SavingsPeriodEn dDate]) & "#')"

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

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

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.Cont rolSource = and t=DSum('[CostPerPaycheck]','tblSavingsFu nds','[SavingsPeriodEn dDate] >= #" & 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.Cont rolSource = "=DSum('[CostPerPaycheck]','tblSavingsFu nds','[SavingsPeriodEn dDate] >= #" & 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.Cont rolSource = "=DSum('[CostPerPaycheck]','tblSavingsFu nds',[SavingsPeriodEn dDate] >= #" & 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!