recurring event

[Guest]

Hello,

In my purchasing database I'd like to keep track of standing orders. Is
there some function or code i can use on a form to predict when my next order
will arrive? I have a table that stores the starting date and the weekly
interval.

Thanks

 

[John W. Vinson]

Hello,

In my purchasing database I'd like to keep track of standing orders. Is
there some function or code i can use on a form to predict when my next order
will arrive? I have a table that stores the starting date and the weekly
interval.

Thanks

Sure; you can use a Query including a call to the DateAdd function to bring up
the next sequential date.

One handy way to do this is to have a multipurpose utility table Num, with one
Long Integer field N; fill it with values from 0 to 10000 or so. Create a
Query with a calculated field

NextOrder: DateAdd("d", [IntervalInDays] * N, [StartDate])

with a criterion of (say) Between Date() And Date() + 7 to see the next week's
orders.

John W. Vinson [MVP]

 

[Guest]

Gen,

One way is to create a public function in a global module and call it in the
formula of a calculated query field:

Public Function NextDeliveryDate(dteStart As Date, intInterval As Integer)
As Date

Dim i As Integer
Dim dteToday As Date

i = 0
dteToday = Date

Do
i = i + 1
NextDeliveryDate = dteStart + i * intInterval * 7

Loop Until NextDeliveryDate >= dteToday

End Function

In the calculated field, pass the values of the starting date and the weekly
interval:

NextDelivery: NextDeliveryDate([YourStartDateField], [YourWeeklyInterval])

Then base your form on the query and place a textbox for the calculated
field just like any other. Alternatively, you can call the function in the
ControlSource of a textbox:

= NextDeliveryDate([YourStartDateField], [YourWeeklyInterval])

Hope that helps.
Sprinks

 

[Guest]

Gen,

I realized after my post that the code will generate an error if the start
date or weekly interval is Null. To avoid the error, use an IIf function
call that assigns a calculated value of Null if either the starting date or
the weekly interval is Null:

NextDelivery: IIf(Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0,Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

Sprinks

 

[Guest]

This seems to work, but I keep getting a "Run Time error: Overflow" when I
run it. Also, where should I put the iif statement? Before or after Do?

Gen,

I realized after my post that the code will generate an error if the start
date or weekly interval is Null. To avoid the error, use an IIf function
call that assigns a calculated value of Null if either the starting date or
the weekly interval is Null:

NextDelivery: IIf(Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0,Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

Sprinks

Hello,

In my purchasing database I'd like to keep track of standing orders. Is
there some function or code i can use on a form to predict when my next order
will arrive? I have a table that stores the starting date and the weekly
interval.

Thanks

 

[Guest]

Nevermind, I put it into the query and it worked perfectly. Thanks a bunch!

This seems to work, but I keep getting a "Run Time error: Overflow" when I
run it. Also, where should I put the iif statement? Before or after Do?

Gen,

I realized after my post that the code will generate an error if the start
date or weekly interval is Null. To avoid the error, use an IIf function
call that assigns a calculated value of Null if either the starting date or
the weekly interval is Null:

NextDelivery: IIf(Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0,Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

Sprinks

Hello,

In my purchasing database I'd like to keep track of standing orders. Is
there some function or code i can use on a form to predict when my next order
will arrive? I have a table that stores the starting date and the weekly
interval.

Thanks

 

[Guest]

Gen,

The function code does not check for a valid date and a valid weekly
interval, therefore you must check for it; that is the purpose of the IIf
statement, which is not meant to be placed in the function code itself but
rather as an expression for a calculated field in a query or as the Control
Source of a form control.

Otherwise, if you're doing data entry and enter a starting date, with the
interval blank or 0, the function will loop continually because the function
value never exceeds the current date. Eventually, Access runs out of memory
and triggers the overflow error.

Set a calculated query field as follows, then set a textbox' ControlSource
to the name of the field (NextDeliveryDate):

NextDeliveryDate: IIf((Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0),Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

OR

set the textbox ControlSource directly to the value of the expression:

=IIf((Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0),Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

I've added basic error-checking to the function code below. I also realized
that if you enter a future starting date, it will return one interval past
the starting date as the next shipment. If you'd prefer it return the
starting date, include the optional
If dteStart > dteToday ..End If block, otherwise, delete it:

Public Function NextDeliveryDate(dteStart As Date, intInterval As Integer)
As Date
On Error GoTo Err_Handler
' Note: Function does not check for valid date and integer parameters

Dim i As Integer
Dim dteToday As Date

i = 0
dteToday = Date

' Start of optional block to test for future starting date
If dteStart > dteToday Then
dteStart = dteStart - intInterval * 7
End If
' End of optional block

Do
i = i + 1
NextDeliveryDate = dteStart + i * intInterval * 7

Loop Until NextDeliveryDate >= dteToday

Err_Exit:
Exit Function

Err_Handler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Err_Exit

End Function

Hope that helps.
Sprinks

This seems to work, but I keep getting a "Run Time error: Overflow" when I
run it. Also, where should I put the iif statement? Before or after Do?

Gen,

I realized after my post that the code will generate an error if the start
date or weekly interval is Null. To avoid the error, use an IIf function
call that assigns a calculated value of Null if either the starting date or
the weekly interval is Null:

NextDelivery: IIf(Nz([StartingDate])=0 Or
Nz([WeeklyInterval])=0,Null,NextDeliveryDate([StartingDate],[WeeklyInterval]))

Sprinks

Hello,

In my purchasing database I'd like to keep track of standing orders. Is
there some function or code i can use on a form to predict when my next order
will arrive? I have a table that stores the starting date and the weekly
interval.

Thanks