Running tally on form

[Mackster66]

MS Access 2003 front end, Sql back end, multiple user environment.

Info:
I have a form used for auditing evidence. It is based on a select query
whose criteria is the storage location [STORAGE]. A barcode is scanned which
inputs the unique identifier [ID] for the item. VBA code then determines if
the item is still active or needs to be destroyed based on the status of the
evidence [DISPOSITION]. If it is active ([DISPOSITION]=0), several fields
are populated with information to show that the item has been audited, who
audited it, and at what time. If it is to be destroyed ([DISPOSITION]=15),
then several fields are populated to show that the item has been destroyed,
who destroyed it, the time it was destroyed, and then the disposition is
changed to 1.

Question:
Is there a way to display the number of records remaining to be audited or
destroyed in the current storage location? The criteria for active records
needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for
items to be destroyed would be [DISPOSITION]=15. The primary key for the
table is [ID].

 

[Marshall Barton]

MS Access 2003 front end, Sql back end, multiple user environment.

Info:
I have a form used for auditing evidence. It is based on a select query
whose criteria is the storage location [STORAGE]. A barcode is scanned which
inputs the unique identifier [ID] for the item. VBA code then determines if
the item is still active or needs to be destroyed based on the status of the
evidence [DISPOSITION]. If it is active ([DISPOSITION]=0), several fields
are populated with information to show that the item has been audited, who
audited it, and at what time. If it is to be destroyed ([DISPOSITION]=15),
then several fields are populated to show that the item has been destroyed,
who destroyed it, the time it was destroyed, and then the disposition is
changed to 1.

Question:
Is there a way to display the number of records remaining to be audited or
destroyed in the current storage location? The criteria for active records
needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for
items to be destroyed would be [DISPOSITION]=15. The primary key for the
table is [ID].

Try adding two text boxes to the form header or footer
section. Set one with an expression like:
=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
and the other:
=Sum(IIf( [DISPOSITION]=15, 1, 0))

 

[Mackster66]

MS Access 2003 front end, Sql back end, multiple user environment.

Info:
I have a form used for auditing evidence. It is based on a select query
whose criteria is the storage location [STORAGE]. A barcode is scanned which
inputs the unique identifier [ID] for the item. VBA code then determines if
the item is still active or needs to be destroyed based on the status of the
evidence [DISPOSITION]. If it is active ([DISPOSITION]=0), several fields
are populated with information to show that the item has been audited, who
audited it, and at what time. If it is to be destroyed ([DISPOSITION]=15),
then several fields are populated to show that the item has been destroyed,
who destroyed it, the time it was destroyed, and then the disposition is
changed to 1.

Question:
Is there a way to display the number of records remaining to be audited or
destroyed in the current storage location? The criteria for active records
needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for
items to be destroyed would be [DISPOSITION]=15. The primary key for the
table is [ID].

Try adding two text boxes to the form header or footer
section. Set one with an expression like:
=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
and the other:
=Sum(IIf( [DISPOSITION]=15, 1, 0))

That works great except I left out an important part of the criteria for
active records. I need [AUDITTIME]<DATE()-180 AND [DISPOSITION]=0. The
following expression is exactly what I needed:

=Sum(IIf([AUDITTIME]<Date()-180 And [DISPOSITIO]=0,1,0))

Thank you very much for your help!

 

[Marshall Barton]

MS Access 2003 front end, Sql back end, multiple user environment.

Info:
I have a form used for auditing evidence. It is based on a select query
whose criteria is the storage location [STORAGE]. A barcode is scanned which
inputs the unique identifier [ID] for the item. VBA code then determines if
the item is still active or needs to be destroyed based on the status of the
evidence [DISPOSITION]. If it is active ([DISPOSITION]=0), several fields
are populated with information to show that the item has been audited, who
audited it, and at what time. If it is to be destroyed ([DISPOSITION]=15),
then several fields are populated to show that the item has been destroyed,
who destroyed it, the time it was destroyed, and then the disposition is
changed to 1.

Question:
Is there a way to display the number of records remaining to be audited or
destroyed in the current storage location? The criteria for active records
needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for
items to be destroyed would be [DISPOSITION]=15. The primary key for the
table is [ID].

Try adding two text boxes to the form header or footer
section. Set one with an expression like:
=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
and the other:
=Sum(IIf( [DISPOSITION]=15, 1, 0))
.

That works great except I left out an important part of the criteria for
active records. I need [AUDITTIME]<DATE()-180 AND [DISPOSITION]=0. The
following expression is exactly what I needed:

=Sum(IIf([AUDITTIME]<Date()-180 And [DISPOSITIO]=0,1,0))

Great. Now that you have the general idea down, there are
many other ways to get the same result and may be a little
faster. For example, in increasing order of efficiency and
decreasing order of obscurity ;-)

=Count(IIf([DISPOSITION]=15,1,Null))
or
=Abs(Sum([DISPOSITION]=15))
or
=-Sum([DISPOSITION]=15)

I don't thing the speed differences are significant so pick
one that resonates with your way of looking at the world and
go with it.