Help with Date Calculation Error (1 Viewer)

[daveUK]

Hi Guys

I've created a form with 3 textboxes, TxtStart, TxtEnd and TxtTime. The user enters the start date, end date and the amount of days that the job takes. TxtStart and TxtEnd use a short date input mask.

After the user presses the 'Search' button a query runs and a report opens, which shows the duration of the jobs between the start and end dates. i.e. if TxtTime has the value 3, then the report will show all of the jobs that took more than 3 days.

This seemed to be working OK, but now I get error 3071 - "The expression is typed incorrectly, or it is too complex..etc".

I'm using a module so that only working days are counted and weekends are not included in the duration of jobs.

Here is the SQL of the query:

SELECT TblPhotocopy.[Start Date], TblPhotocopy.[Completion Date], TblPhotocopy.Operator, TblPhotocopy.[Staff Name], TblPhotocopy.Campus, TblPhotocopy.[Cost Code], TblPhotocopy.Paper, TblPhotocopy.[No of Originals], TblPhotocopy.[No of Copies], TblPhotocopy.[Other Materials], TblPhotocopy.[Other Material Amount], TblPhotocopy.[Discount Amount], TblPhotocopy.[Total Cost], WorkDayDifference([Start Date],[Completion Date]) AS Duration
FROM TblPhotocopy
WHERE (((TblPhotocopy.[Completion Date]) Between [Forms]![FrmDateSearch]![TxtStart] And [Forms]![FrmDateSearch]![TxtEnd]+1) AND ((WorkDayDifference([Start Date],[Completion Date]))>[Forms]![FrmDateSearch]![TxtTime]));

Here is the module code:

Function WorkDayDifference(TxtStart As Date, TxtEnd As Date) As Long

Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long

lngTotalWeeks = DateDiff("w", TxtStart, TxtEnd)
lngTotalDays = lngTotalWeeks * 5
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), TxtStart)

While dtNominalEndDay <= TxtEnd
If Weekday(dtNominalEndDay) <> 1 Then
If Weekday(dtNominalEndDay) <> 7 Then
lngTotalDays = lngTotalDays + 1
End If
End If
dtNominalEndDay = dtNominalEndDay + 1
Wend

WorkDayDifference = lngTotalDays - 1

End Function

Any help is greatly appreciated.

Dave

 

[daveUK]

nobody????

 

[AncientOne]

At what point is the error generated? In the module or the SQL?

 

[daveUK]

Thanks for the reply AncientOne, but for some reason it seems to be working now. I can't understand why, 'cos I haven't changed anything. It was working on Wednesday, Thursday it wouldn't work, and today it's working!

Thanks for your help anyway.

Dave