AutoSum: IIf fields

[Guest]

Hello.
I am using Access 2000 and I need help autosumming a field.
In my report have a few field including "Office" and "InitiateDate". The
Office field could contain LO, SB, SM, or Not Assigned and the IntiateDate
field contains dates i.e. 01/01/2006. I need to add the fields up so I
created a control text field and put this in it =IIf([Office]="LO" And
IsNull([InitiateDate]),0,1). In this case I want to count records with the
Office as "LO" and that have a date in the InitiateDate field. On this
control box I have set it to Running Sum-Over All. This control box is
located in the Detail section. I have created another control box in the Page
Footer to autosum it all and it is written like this =[LO Initiate Run Sum]
which means that it is looking at the control box with the formula above. The
Page Footer box Running Sum is set to Over All. The problem is that the Page
Footer box comes up with a count of 297 but that is how many records there
are total. It should come up with 2
Could you tell me what is wrong?

Thanks.
Iram

 

[Jeff Boyce]

Iram

If you are trying to count something, consider using a Totals query,
selecting the records with [Office] = "LO" and the [InitiateDate] Is Not
Null.

Or consider using a DCount() function to count the number of records that
match your criteria...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff. Could you give me a few pointers on how to do both ways that you
suggested?

Thanks.
Iram

Iram

If you are trying to count something, consider using a Totals query,
selecting the records with [Office] = "LO" and the [InitiateDate] Is Not
Null.

Or consider using a DCount() function to count the number of records that
match your criteria...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello.
I am using Access 2000 and I need help autosumming a field.
In my report have a few field including "Office" and "InitiateDate". The
Office field could contain LO, SB, SM, or Not Assigned and the IntiateDate
field contains dates i.e. 01/01/2006. I need to add the fields up so I
created a control text field and put this in it =IIf([Office]="LO" And
IsNull([InitiateDate]),0,1). In this case I want to count records with the
Office as "LO" and that have a date in the InitiateDate field. On this
control box I have set it to Running Sum-Over All. This control box is
located in the Detail section. I have created another control box in the
Page
Footer to autosum it all and it is written like this =[LO Initiate Run
Sum]
which means that it is looking at the control box with the formula above.
The
Page Footer box Running Sum is set to Over All. The problem is that the
Page
Footer box comes up with a count of 297 but that is how many records there
are total. It should come up with 2
Could you tell me what is wrong?

Thanks.
Iram

 

[Jeff Boyce]

Iram

Open a query in design mode. Add the table and fields. Click on the Totals
toolbar button. Use "Where" for the selection criteria. Use "Count" for a
field that you know will have something (like the ID field).

or

Check Access HELP on the syntax for the DCount() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff. Could you give me a few pointers on how to do both ways that
you
suggested?

Thanks.
Iram

Iram

If you are trying to count something, consider using a Totals query,
selecting the records with [Office] = "LO" and the [InitiateDate] Is Not
Null.

Or consider using a DCount() function to count the number of records that
match your criteria...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello.
I am using Access 2000 and I need help autosumming a field.
In my report have a few field including "Office" and "InitiateDate".
The
Office field could contain LO, SB, SM, or Not Assigned and the
IntiateDate
field contains dates i.e. 01/01/2006. I need to add the fields up so I
created a control text field and put this in it =IIf([Office]="LO" And
IsNull([InitiateDate]),0,1). In this case I want to count records with
the
Office as "LO" and that have a date in the InitiateDate field. On this
control box I have set it to Running Sum-Over All. This control box is
located in the Detail section. I have created another control box in
the
Page
Footer to autosum it all and it is written like this =[LO Initiate Run
Sum]
which means that it is looking at the control box with the formula
above.
The
Page Footer box Running Sum is set to Over All. The problem is that the
Page
Footer box comes up with a count of 297 but that is how many records
there
are total. It should come up with 2
Could you tell me what is wrong?

Thanks.
Iram