Using the Dsum in a report 8

[chubby]

JoeMiller, you have been a great help and I just wanted to thank you even if I can't get this work. I still can't get the results my end-users want. I can't add the totals from two different queries together.

I tried this:

1. I made two queries to get total # of officers and the to get total number of personnel at UIC 2253.

2. Next used an unbound text box in my report.

3. Then I used this statement: =DSum("[Officer]+[UIC 2253]&quot

4. I just want to add these two totals together in my report, but NO CAN DO!!!

I have a number of combinations I need to automatically add up all from a number of text base count queries.

I keep getting a arugment error or #Name error. What i'm I doing wrong??? I really need this to work!!!

 

[LarryDeLaruelle]

Chubby:

I don't think you can do math in a DSum.

You may need to use two DSums to capture the individual values from each of the queries.

The syntax for DSum is DSum(expression, domain) where expression is the field name and domain is the data source (table or query).

So, DSum("[Officer]", "qbeOfficer&quot would get the Officer number; DSum("[UIC 2253]", "qbeUIC2253&quot would get the second value.

Create an unbound text control to get the sum of the two.

Hope that helps.
Larry De Laruelle
larry1de@yahoo.com

 

[JoeMiller]

Chubby,

Larry is right, you cannot do math in a DSum. Dsum goes to a table/query, takes the column/fieldname that you are referencing and adds up every single record in that column/fieldname. If you provide criteria it will not add up every single record, but only those records where the criteria matches. After reading your post it seems that you want a sum of officers and personnel at a certain building UC 2253, or a "head count" if you will. So let's take Larry's stuff and modify something, we're not going to use DSum, but instead DCount to get the number of personnel at UC 2253.

Now let's take the two queries we've got, one for personnel (let's call it UC2253_Personnel), and one for officers (let's call it UC2253_Officers). In those queries should be a UniqueID for every record, the UniqueID is the field we are going to count to get our totals. So now on to our text boxes on the report.

[tt]
Description: PersonnelHeadCount
ControlName: TextBox1
ControlSource: =DCount("[UniqueID]","UC2253_Personnel&quot

Description: OfficerHeadCount
ControlName: TextBox2
ControlSource: =DCount("[UniqueID]","UC2253_Officers&quot
[/tt]

And now we will make a third textbox for our TotalHeadCount of Personnel and Officers at UC2253.

[tt]
Description: TotalHeadCount
ControlName: TextBox3
ControlSource: =[TextBox1]+[TextBox2]
[/tt]

So there is what is hopefully the answer to your question. A quick word, you don't have to do this in three separate boxes, you can just cut to the chase and make a control that does both the DCounts at one time and then adds them together, like so:

[tt]
Description: CutToTheChaseAndAddEmBoth!
ControlName: CutToTheChase
ControlSource: =DCount("[UniqueID]","UC2253_Personnel&quot+DCount("[UniqueID]","UC2253_Personnel&quot
[/tt]

Hope that helps! (my fingers hurt ;-))
Joe Miller
joe.miller@flotech.net

 

[LarryDeLaruelle]

Joe:

Cool solution. I learned something new today.

Thanks.
Larry De Laruelle
larry1de@yahoo.com

 

[JerryDennison]

I'm sorry to disagree but you CAN do math in DSUM(). Chubby's problem was he didn't define the recordset. It should have been:

=DSum("[Officer]+[UIC 2253]", "TableName&quot

Where TableName is the name of the table with the fields Office and UIC 2253

 

[JoeMiller]

Thanks Jerry! I just learned something new, that's what I get for implicitly believing in Microsoft help! ;(

Joe Miller
joe.miller@flotech.net