Prev: Using Access to create an appointment and add attendees
Next: The data has been changed message
From: Mackster66 on 19 May 2010 09:22
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].


--
I'm not young enough to know everything.
From: Marshall Barton on 19 May 2010 10:24
Mackster66 wrote:

>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))

--
Marsh
MVP [MS Access]
From: Mackster66 on 19 May 2010 11:37


"Marshall Barton" wrote:

> Mackster66 wrote:
>
> >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))
>
> --
> Marsh
> MVP [MS Access]
> .

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!


From: Marshall Barton on 19 May 2010 13:56
Mackster66 wrote:
>"Marshall Barton" wrote:
>> Mackster66 wrote:
>> >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.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Using Access to create an appointment and add attendees
Next: The data has been changed message