Using DSum in a text box (1 Viewer)

dragnsteph

Registered User.
Local time
Today, 08:44
Joined
Jun 18, 2015
Messages
19
Ok, I'm not sure if I'm approaching this the right way or not. I've never done anything like this with Access before and a lot of the jargon is very unfamiliar and hard to sort through.

I have a volunteer information database, and I am working on the form that will show the details of each volunteer's hours worked, among other things.

I've attached screenshots of a more or less final version of the Relationships (I've tweaked it a little in the last day or so but nothing life-altering), the section of the form in question, and a query I wrote (probably incorrectly) that does return hours on a given month sorted by NameID, which is helpful, but doesn't put the information into fields like I'd imagined I could. If I can't figure out how to make what I'd like work, I guess I could just put a subform that displays the results of the query, but I'd rather do it another way.

I tried this in the controls for each field:
Code:
DSum( [tblHoursWorked]![MonthWorked] , "MonthWorked='November'")
obviously switching out the months, but I'm getting the response of #Name? in each field. No syntax error so I'm not sure if I have something configured incorrectly in the text boxes or if there's a problem with the function.

I'm not sure if I'm even approaching this the right way. A lot of the tutorial websites kind of gloss over things like even understanding what the syntax should mean, and I really would appreciate it if someone could dumb it down for me :) Let me know if you need more information.

Thanks,
Stephanie
 

Attachments

  • relationships2.png
    relationships2.png
    43 KB · Views: 93
  • Hours Current Year.png
    Hours Current Year.png
    12.4 KB · Views: 82
  • qryVolunteerHoursByMonth.png
    qryVolunteerHoursByMonth.png
    14.3 KB · Views: 151

dragnsteph

Registered User.
Local time
Today, 08:44
Joined
Jun 18, 2015
Messages
19
Any chance you could explain? Obviously I've seen those sorts of examples and already done it wrong. Not sure what needs to be different. I had the table name in there separately once and it gave me an error.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:44
Joined
Aug 30, 2003
Messages
36,118
There are 3 arguments, not 2. The field name in quotes, then the table name in quotes, then the criteria (which you have right).
 

dragnsteph

Registered User.
Local time
Today, 08:44
Joined
Jun 18, 2015
Messages
19
Ok, when I get to the office I'll try that again. Seems like that's what I had at first and I got a syntax error, maybe I missed a quote. Thank you.
 

dragnsteph

Registered User.
Local time
Today, 08:44
Joined
Jun 18, 2015
Messages
19
To my other point, is this even the best way to go about this? I have read a lot of things that make me wonder if using domain aggregate functions is a good idea, or if I would be better off using the subform/query and trying to figure out how to sort it meaningfully.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:44
Joined
Aug 30, 2003
Messages
36,118
They are certainly not the most efficient solution as a rule. I would likely try a totals query that summarized the data and present it in a subform.
 

dragnsteph

Registered User.
Local time
Today, 08:44
Joined
Jun 18, 2015
Messages
19
Like the one I attached the screenshot of? I got that working, but wasn't able to figure out how to sort the data by month and year.

Now that I think about it, I have to filter out the previous years, too, and then re-collect that data for the previous years field. More thinking needed, I guess.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:44
Joined
Aug 30, 2003
Messages
36,118
That was a totals query, though it doesn't show anything being summed. You may be able to sort on a field formatted as yyyymm.
 

murray83

Games Collector
Local time
Today, 16:44
Joined
Mar 31, 2017
Messages
728
quick question

is the criteria needed as im just trying to sum number of pallets on to front page of form so people are aware when trailer is full

here is my code

Code:
=DSum("PalletCount",[tbl_Returns])
have tried replacing the " with ' and still just get the good old #NAME fault

any help much appreciated :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:44
Joined
Aug 30, 2003
Messages
36,118
You realize that will sum the entire table without a criteria?
 

Users who are viewing this thread

Top Bottom