Using an if criteria with Dcount or count function

[Triacona]

Dear all,

I have the following problem...

I have a query that generates for all records (OpenRpCases)
The main fields are:
REFVAL (UNI7LIVE_RPINFO) (table) UNI7LIVE_RPINFO.REFVAL(cases)
REFVAL (UNI7LIVE_XIREC) (table) UNI7LIVE_XIREC.REFVAL(Subcases)
VOFF (UNI7LIVE_XIVIST) (table) UNI7LIVE_XIVIST.VOFF(Officer)


I have a query that lists all the cases so I can count them(RpList) this data is pulled from the (OpenrpCase)

#as the main query (OpenRpCase) would generate more records due to the fact that it has sub cases#

I have a query that lists all the subcases for counting purposes. (RpIvaList)

I have run a report(OpenRpCases)off the query (OpenRpCases)

I have included totals of the other two queries.
I have used the Dcount function. That works o.k.

I have a query that lists all the cases for counting purposes. (TEST2)

I now have inserted another field into each of those count queries (RpList)(RpIvaList)

This field is called VOFF.

I now want to use the Dcount function in collaboration with an iff statement, for Each officer (VOFF)

This is what I have come up with:

=IIf("VOFF"="EH_EL",DCount("*","RpList","UNI7LIVE_RPINFO.REFVAL"))

I have also tried this:

=DCount("UNI7LIVE_XIVIST.VOFF"="EH_EL","RpIvaList","UNI7LIVE_XIREC.REFVAL")

or this

=DCount("EH_EL","RpList","UNI7LIVE_RPINFO.REFVAL")

or this...

=IIf("UNI7LIVE_XIVIST.VOFF"="EH_EL",DCount("*","TEST2","UNI7LIVE_RPINFO.REFVAL"),0)

This in the VOFF header

=DCount("*","TEST2","UNI7LIVE_RPINFO.REFVAL")

Or this in the VOFF header

=DCount("EH_EL","TEST2","UNI7LIVE_RPINFO.REFVAL")

This is to bring up how many cases are open by a certain officer (in this case EH_EL)

This comes up with an error or the wrong number.

My report has serval levels:
Report Header
Page Header
VOFF Header (officers)
Address Header
UNI7LIVE_RPINFO.REFVAL Header (cases)
UNI7LIVE_XIREC.REFVAL (subcases)
Detail
Page Footer

I have put this code in a text box, in the following levels:
Report header
Page header
VOFF header (in desperation)

Have I gone completely wrong???
I have also created a test table with the original tables in, I did not use the OpenRpCases query.
This still gives me errors, it either displays nothing, or it displayes error, or it displays the wrong number of cases, either listing all of them or an inaccurate number.
If I can use the VOFF header to curtail the number according to officer (VOFF) that will be great, but at the moment it is not working...

Please, please help, this one is driving me around the bend.

Kind regards
Triacona

 

[jrbarnett]

If I understand you correctly, you want a count of records per officer (VOFF Header).
If this is the case then you can add a textbox bound to =Count(*) and let the Access reporting engine sort this out.

If you want it at the bottom of the VOFF group, add a group footer and put it in there.

John

 

[Triacona]

Thank you very much for your reply John
I have restructured my queries and report...easier to manage.

I have another question; I have a query (RpIvaCount)

That query has two tables RPINFO and XIREC.
Those tables are linked as follows:
One RPINFO case for many XIREC cases (One to many relationship)

The RPINFO case ref no field is:
UNI7LIVE_RPINFO.REFVAL (UNI7LIVE_RPINFO is Table)
The XIREC case reference no field is:
UNI7LIVE_XIREC.REFVAL (UNI7LIVE_XIREC is table)

So I get replication/duplication.
I.E. 06/00001/HMO has two child reference no's
06/00499/HMO
06/00498/HMO
So it will appear twice in the list, therefore counted twice.

So my question is how do I count only the one reference no. UNI7LIVE_RPINFO.REFVAL, with the Dcount function, without counting the replicates/duplicates?

I tried the below but it counted the duplicates.

=dcount("*","RpIvaCount","UNI7LIVE.REFVAL")

Your help will be greatly appreciated.

Kind regards
Triacona