MIN, MAX on DATE

[jyotisb]

Hello,

This is my 1st post.

My table looks like following

Code Start_Date Finish_Date Duration
K108 12/4/2013 8:00:00 AM 2/3/2014 5:00:00 PM 8.8 wks
K108 12/4/2013 8:00:00 AM 2/3/2014 5:00:00 PM 8.8 wks
K108 12/4/2013 8:00:00 AM 2/3/2014 5:00:00 PM 8.8 wks
K109 2/6/2014 8:00:00 AM 3/5/2014 5:00:00 PM 4 wks
K109 2/6/2014 8:00:00 AM 3/5/2014 5:00:00 PM 4 wks
K109 2/6/2014 8:00:00 AM 3/5/2014 5:00:00 PM 4 wks
K109 1/2/2014 8:00:00 AM 2/5/2014 5:00:00 PM 5 wks

Now on my form which is based on the table, I need to have a start date and Finish date. For Code-K108, its pretty straight fwd. But for code-K109, I need to have the 'Start Date' as '1/2/2014 8:00:00 AM' and 'Finish Date' as '3/5/2014 5:00:00 PM'. Also the Duration field should be the weeks between ''1/2/2014 8:00:00 AM' and '3/5/2014 5:00:00 PM'. How can I do this? Please help.

 

[madefemere]

Hello,

1 - You create a textbox that looks up the Min of the Start_Date for every CODE for e.g named TxtMIN with DLookUp function :

= Dlookup ("Start_Date","YourTable","[CodeNum]=" & CodeControlInForm

2 - You create a similar textbox with the similar source to find the max of the Finish_Date for every CODE, named TxtMAX.
3 - You create a new textbox and use the DateDiff function to calculate the difference between these dates in weeks :

 =DateDiff ("w",[TxtMAX],[TxtMIN],1)

It should be good.

 

[Brianwarnock]

Domain functions are heavy on resources so why not base your form on a query which groups on code and min on startdate and max on enddate .

Brian