Standing Order date calculation

[gillianleec]

I am having trouble getting started on this project and would appreciate some direction. I have a table for Standing Orders where the user enters a client and the timeframe for which their orders should be generated. For example, they want their order bimonthly, the user would select Timeunit=month and Timenum=2:
ID
Client
Startdate
TimeUnit (week, month)
TimeNum (1,2,3,4)
Quantity

I would like to make a query that would run daily and identify Standing Orders that are due and write them to an orders table. The query would look at the day/month of the Startdate and then calculate the next due date for an order based on TimeUnit and TimeNum.

I know that I would probably use the DateAdd Function but how do I take into consideration the current date?

Gillian

 

[SkipVought]

Hi,

You compare the calculated (DateADD) date to Today. If that date >= today, then you kick off the order.

Skip,
[sub]
[red][/red]
[/sub]

 

[gillianleec]

Thanks for responding so quickly.

I guess my question is more on how to get the calculated date.

If I use the startdate as my base date:
Dateadd(TimeUnit, startdate, TimeNum) then the date won't progress past the first order. How do I continue the process?

 

[gillianleec]

I think I might have figured it out

if
(DateDiff([timeunit],Date(),[startdate])) Mod [timenum]=0
Then ....'include order