Hello,

I'm running Access 2002.

We use our database to print address labels and despatch order's live from
the form as they are packed and labelled.
All records in the query should be 'despatched' (therefore no longer in the
query) before 4:30PM.

I would like to be able to take the current time and the deadline (4:30PM),
have Access work out how many minutes is left between now and the deadline,
then divide this by how many orders (records) are in the query.

Example: Current time is 2pm, deadline is 4:30pm. There is 300 orders
outstanding (300 records in the query) that have not been despatched. So
that is 150 minutes between 2pm and 4:30pm, divided by 300 orders = 0.5
(half a minute or 30 seconds).

The result is the user will know an order needs to be despatched every x
seconds to be finished by 4.30pm. Encouraging the user to work faster if
required.

I would be grateful if anybody who has done this before can throw me a few
tips or a link.

Many Thanks...
Nick Jones

Nick Jones wrote:

I did this with varibles to demonstrate.

T1 = Now() 'current time
T2 = Dateadd("m",38,T1) 'add 38 minutes to current time
NumOrders = 5 'number of orders

? DateDiff("m",T1,T2) 'calc time diff
38
? DateDiff("m",T1,T2)\NumOrders 'integer divide orders
7
? DateDiff("m",T1,T2)/NumOrders 'with decimal divide of orders
7.6

Uh, Salad. I think you added 38 months, not 38 minutes.
DateAdd("n",38,Now()) would add 38 minutes.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 11/14/2010 7:13 PM, Salad wrote:

On 15/11/2010 13:56:12, John Spencer wrote:
Try

Function PackRate(ToPack As Integer) As Integer

Dim ClosingTime As Date
Dim SecsLeft As Integer

ClosingTime = CDate(CStr(Date) & " 16:30")

SecsLeft = DateDiff("s", Now, ClosingTime)
PackRate = SecsLeft \ ToPack

End Function

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Hi Guys,

Thanks for your responses...Phil, thanks for sharing the function.

I have created a module to put this in, it's been a long time since I played
with Access, I would like the PackRate to be displayed in a control on a
form. I think you have done the hard bit for me, could anyone give me a
quick guide on how to intergrate this function into a form.

ToPack is the number of records in the query, I also have this displayed in
a control box on the form

Function PackRate(ToPack As Integer) As Integer

Dim ClosingTime As Date
Dim SecsLeft As Integer

ClosingTime = CDate(CStr(Date) & " 16:30")

SecsLeft = DateDiff("s", Now, ClosingTime)
PackRate = SecsLeft \ ToPack

End Function

Many, many thanks...
Nick Jones

On 19/11/2010 23:32:47, "Nick Jones" wrote:
withh all the calls from queries), but name doesn't matter - Oh you already
have - just add the word Public Public Function PackRate(ToPack As Integer)
As Integer ...
End Function

Then in your underlying query for your form, add a field
RateToPack:PackRate(ToPack) - just like that when you run your query, you
should see the RateToPack as any other field so just add it to your form. I
tend to show fields that cant be edited in a different format to those that
need to be filled in, and as this is a calculated field, it obviously can't
be edited

Phil
Easiest way is in your underlying quety