report filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 16 Nov 2004 22:00 GMT

I accidently posted this request yesterday to
microsoft.public.access.gettingstarted. Please forgive my
crossposting.

Hi

I'm using Access 2000 to create a report that displays order
information for data entry. If there are 90 orders today and I have 4
associates available for data entry, I print the report and sort it
manually so each associate gets 23 or 24 orders.

I'm trying to find a way to automate this filtering. I'm thinking of
adding another field to the underlying table and update that field
with a 1, 2, 3 or 4. Then use that value to filter the report. I don't
know how to populate that field using a query.

The available associate quantity is not the same each day. Some days
there are two associates available. I could have up to 5 associates
available.

Any suggestions?

Dan

Reply to this Message

Allen Browne - 17 Nov 2004 03:28 GMT

The next number would be given by an expression such as this:
(Nz(ELookup("AssignTo", "OrderTable", "[OrderDate] = " & Date, "[ID]
DESC"), -1) + 1) Mod 4

where the ELookup() is copied from:
http://members.iinet.net.au/~allenbrowne/ser-42.html

It means, lookup the AssignTo field, in the OrderTable, for todays date,
matching the highest autonumber. If there was none, use -1. Add 1 to the
result. Then give me the remainder when divided by 4.

This should cycle through the numbers 0 to 3.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I accidently posted this request yesterday to
> microsoft.public.access.gettingstarted. Please forgive my
[quoted text clipped - 19 lines]
>
> Dan

Reply to this Message

Dan - 18 Nov 2004 01:01 GMT

Thanks for your feedback Allen. I did not use your Elookup function.
However, I used the mod function in an update query and that met my
needs. Again, Thank you. Dan

> The next number would be given by an expression such as this:
> (Nz(ELookup("AssignTo", "OrderTable", "[OrderDate] = " & Date, "[ID]
[quoted text clipped - 32 lines]
> >
> > Dan

Reply to this Message