Find date record in calendar table based on Start Date and # of Days to add

I have a calendar table with calendar.calendardays (every day of the year), calendar.Weekdays (Monday - Sunday) and calendar.OfficeWorkdays (states whether it is a workday, holiday, weekend or closure).

I have a delphi form where user inputs the Start Date and # of Days in Mfg.  What I would like to happen on EditValueChange is to calculate the End Date based on (Start Date + # of Days in Mfg) BUT it should only add days that are marked as 'Workday' in the Calendar.OfficeWorkdays field.

For example, start date on my test record is 11/15/11. When I enter 2, the result should be 11/16/11 BECAUSE the Start Date is INCLUDED in the 2 days. Another example with the same start date of 11/15 and user enters in 5, resulting date should be 11/21/11. 15th is day 1, 16th is day 2, 17th is day 3, 18th is day 4, SKIPS 19th and 20th because they are in Calendar.OfficeWorkdays as 'Weekend' and the 21st is the next 'Workday' and that is day 5.

Below code is my attempt... but as soon as I hit a non-Workday, I get incorrect results. What I tried to do is get the count of non-Workdays, get the start date and get the # of days to add, then ADD that all together but what happens is I end up on a Sunday. I also have a CalendarView I was going to test out and it is simply the Calendar table but filtering only for OfficeWorkdays = 'Workday'.

Thank you for your help!

procedure TfrmMfgScheduleForm.edMfgDaysPropertiesEditValueChanged(
  Sender: TObject);
var sMfgStart, sDays, sSql, sDaysToAdd, sMfgComplete : String;  dMfgStart, dDays, dMfgComplete, dDaysToAdd :TDateTime;
        fDayToAdd : Variant;
begin
  inherited;

  dMfgStart     := StrToDate(edMfgStart.Text);
//  dMfgComplete  := StrToDate(edMfgTarget.Text);
  fDayToAdd     := StrToFloat(edMfgDays.Text);
//  dDays         := StrToDate(DateToStr(dMfgStart + StrToFloat(edMfgDays.Text)));
  dDays         := dMfgStart + fDayToAdd;

  //how many non-workdays are there between start of mfg plus days to add?
  with dmMfgSchedule.qrySearch do begin
        Close;
        sql.Clear;
//        sql.Add('SELECT CalendarDays FROM CalendarView WHERE CalendarDays = ('''+DateToStr(dDays)+''') ');
        sql.Add('SELECT COUNT(ID) AS Expr1 FROM Calendar ');
        sql.Add('WHERE (CalendarDays BETWEEN '''+DateToStr(dMfgStart)+''' AND '''+DateToStr(dDays)+''') ');
        sql.Add('AND OfficeWorkDays <> ''Workday'' ');
        Open;
        end;
        sDaysToAdd      := dmMfgSchedule.qrySearch.FieldValues['CalendarDays'];
        sMfgComplete    := DateToStr( (fDayToAdd - 1) + (dMfgStart) + (StrToFloat(sDaysToAdd)) );

  //Calculate Mfg Start plus Days in Mfg
  sSql := ' UPDATE PCJobData SET Mfg_Complete = '''+DateToStr(dMfgComplete)+''' ';
  sSql := sSql + ' WHERE ID = '''+sJobID+''' ' ;
  dmMfgSchedule.ADOCommand1.CommandText:= sSql;
  dmMfgSchedule.ADOCommand1.Execute;

Select all Open in new window