By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Dsum as ControlSource for text box on report not working

P: 32
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
Oct 18 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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?
Oct 18 '07 #2

P: 32
Sorry, I got mixed up and typed the wrong field name. SavingsPeriodEndDate is also a field in the table tblSavingsFunds.
Oct 18 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
It should be this:
"# <= [SavingsPeriodEndDate])"
Oct 19 '07 #4

P: 32
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.
Oct 19 '07 #5

patjones
Expert 100+
P: 931
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.
Oct 19 '07 #6

P: 32
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"
Oct 19 '07 #7

P: 32
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')"
Oct 19 '07 #8

Rabbit
Expert Mod 10K+
P: 12,366
Ok, I think I see now. Use this:
Expand|Select|Wrap|Line Numbers
  1. Me.Text127.ControlSource = "=DSum('[CostPerPaycheck]','tblSavingsFunds','[SavingsPeriodEndDate] >= #" & nextpayday & "# and [FundStatus] = ""Waiting""')"
  2.  
Oct 19 '07 #9

P: 32
Yes! That did it. I've never seen that before. Thanks very much everybody, especially Rabbit!
Oct 19 '07 #10

P: 32
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.
Oct 19 '07 #11

Rabbit
Expert Mod 10K+
P: 12,366
Surround that with the Nz function. Nz returns an alternate value if the value you give it is Null.

Nz(DSum(), 0.00)
Oct 19 '07 #12

P: 32
Thanks very much for your help!
Oct 30 '07 #13

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Oct 30 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.