auto populate field

[dsevier]

Im creating a database for a preventative maintenance program where i need it to sort from last_PM_date field and auto create next_PM_date i need it to take today's date and put the oldest record from last_PM_date and schedule next_PM_date for either the next tuesday or thursday
thanks for your help

 

[OnTheFly]

To calculate the new date you would probably want to use a case statement and the Weekday(Date()) function.

Weekday(Date()) will return the integer representing the day of the week (i.e. Sunday=1, Mon=2, Tue=3) etc. From there you can calculate how many days to add to get you to the next Tues or Thursday.

Hope this helps.

OnTheFly

 

[Aerin]

interesting
Lets think

First we probably need to find out the today's day number
dim i as integer
dim NextDate as Date
i=Weekday(Date())
' then compare to know did we missed thursday or tuesday, right?

if (i < 3) then
NextDate = DateAdd("d", (3-i), Date())
end if
if (i >= 5) then
NextDate = DateAdd("d", (3+(7-i)), Date())
end if

if (i => 3) and (i < 5) then
NextDate = DateAdd("d", (5-i), Date())
end if


I am sure it is possible to create more elegant solution, it is just food for thoughts