 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007recurring event
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 >Hello, > [quoted text clipped - 4 lines] > >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] 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 > Hello, > [quoted text clipped - 4 lines] > > Thanks 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, > [quoted text clipped - 4 lines] > > Thanks 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, > [quoted text clipped - 16 lines] > > > > Thanks 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? [quoted text clipped - 19 lines] > > > > > > Thanks 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? [quoted text clipped - 19 lines] > > > > > > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.