Creating a list of scheduled orders with user entered data

[psulion01]

My order management database project continues...everyone here has been helpful thus far so I'll go searching for more assistance...

Is there a way to have Access create a schedule of orders if I input the general parameters, i.e.:

TransactionID: <Autonumber>

Start Date: 1/1/04
End Date: 12/31/04
Maximum Shares: 12,000
Order Frequency: Monthly on the X day of the month
<Frequency> quantity: 1,000 shares
Price: $50.00

Essentially I would want to input the data above and have a separate table populate each individual trade as follows:

TransactionID OrderDate EndDate Quantity Price
22 1/1/04 12/31/04 1,000 $50.00
23 2/1/04 12/31/04 1,000 $50.00
24 3/1/04 12/31/04 1,000 $50.00
25 4/1/04 12/31/04 1,000 $50.00

etc....

Also hoping to build in logic that checks the order date to make sure it falls on a weekday. if it happen to be a saturday, then add 2 days, if a sunday then add 1 day.

Any thoughts on how to do this? I've got a framework set but don't know how to program the user-entered parameters to make the order auto-populate into an 'Orders' table/form.

Thanks!

 

[bat17]

You will probably need to do this with code. You will need to have a custom function to build the order date but more information on rules is needed
What happens if the order date is at the end of the month? If the the date is 30th then what happens in Febuary? What happens if the weekend pushes the date past the end of the month or past the EndDate?
Do public holidays need to be taken into account as well as weekends?

Peter

 

[psulion01]

Re: Creating a list of scheduled orders with user entered da

Peter,

All good points. 99% of the time the orders are to occur on the first available trading day of each month, and only a handful of plans will set a date like the 10th or 15th, etc., so the logic for the last day isn't crucial at this time, although it would be a nice touch.

Basically, the orders must be confined to the Start/End dates of the given transaction, and again, most orders begin trading during the beginning or middle of the month, while the plans and tranactions end on the last day of a given month.

Regarding holidays, I can adjust my queries and reports when I know there's a holiday up ahead (i.e. screen for orders over the week of Christmas). My main concern is being able to manage the large number of orders that I place on the first of each month.

I'd like to start with the code to create orders on the first business day of each month (based on the parameters I mentioned). From there I'll work on tweaking the specifics (the issued you mentioned).

Any thoughts on how to go about this?

Thanks for all of your help!

 

[bat17]

Here are a couple of bits of code to show how you can work your dates. the function just checks for Sat/Sun and adds on if needed. you could call this from a query to sort the date or from another piece of code like the sub does.(pess ctr-G after running to get the debug window up to see its output)

Function NextWorkingDay(ByVal dDate As Date)
If WeekDay(dDate, vbMonday) = 7 Then dDate = dDate + 1
If WeekDay(dDate, vbMonday) = 6 Then dDate = dDate + 2
NextWorkingDay = dDate
End Function


Sub getDates()
Dim StartDate As Date
Dim EndDate As Date
Dim DueDate As Date

StartDate = #1/1/2004#
EndDate = #11/30/2004#

Do While StartDate < EndDate
   DueDate = NextWorkingDay(StartDate)
   Debug.Print StartDate & " -- " & DueDate
   StartDate = DateAdd("m", 1, StartDate)
Loop
End Sub

Peter

 

[psulion01]

Re: Creating a list of scheduled orders with user entered da

ok I've added the function portion of the code to the module in the database. as for the sub, a few questions. Where does the #1/1/2004# and #11/30/2004# come from? don't I want this sub to reference the startdate and enddate fields from my Transactions form? also, where would i place this code and how is it triggered to run, or is it always running in the background?

I see what it's doing, but I need this step to occur while some other code is running to populate the orders over a given date range (i.e. once the transaction form is completed and saved, moved the next record, closed, etc. the code would run and generate the orders for each month, quarter, etc. based on the parameters entered on the transaction form. these orders would be stored on an Orders table which could then be viewed through a subform on the related transactions form.

Confused? I can send a copy of the database as I currently have it...it might make more sense if you can actually see the layout and relationships.

Thx