Re: Expression typed incorrectly or too complex?

Thanks for the replies.

The query works when I do not supply a criteria for the
calculate_working_days field. I have checked for Null values in the date
fields and there doesnt seem to be any. I have also tried changing the data
type being passed back from the function from Integer to Variant and Long.

I have used this function in the same way in the past and it normally works!!

The function is as follows:

Public Function Calculate_Working_Days(StartDate As Date, EndDate As Date)
As Integer

'--------------------------------------------------
'Calculates the correct amount of working days
'between two dates including bank holidays. Bank
'holidays are contained in the table zSYS_tblSpecialDays
'--------------------------------------------------

Dim dayCount As Integer
Dim wkendCount As Long '---- No of weekend days to be subtracted ----
Dim intDiff As Long '---- Number of all days between 2 dates ----
Dim dbldiff As Double
Dim finaldiff As Long '---- Final figure
Dim rstDays As Recordset
Dim SpecCount As Integer '---- Number of special days between the two dates
----
Dim DateRet1 As Date '---- Same date as Date2 ----
Dim date1 As Date
Dim date2 As Date
Dim nulldate As Integer

'Dim db As database

Set db = CurrentDb()
Set rstDays = db.OpenRecordset("zSYS_tblSpecialDays")

Dim rec As Long

DateRet1 = Format(DateRet1, "dd/mm/yyyy")
StartDate = Format(StartDate, "dd/mm/yyyy")

rec = rec + 1

If StartDate <> Empty And EndDate <> Empty Then

If StartDate < EndDate Then

date1 = StartDate
date2 = EndDate

Else
date1 = EndDate
date2 = StartDate
End If

Else
nulldate = 1
End If


If nulldate = 0 Then

intDiff = date2 - date1

wkendCount = 0
SpecCount = 0

Do Until date2 = date1

dayCount = WeekDay(date2)

If dayCount = 1 Or dayCount = 7 Then
wkendCount = wkendCount + 1
End If

'---- Calculates amount of bank holidays between dates ----
' Uses daycount and checks Fridays, Mondays and Tuesdays

If dayCount = 2 Or dayCount = 3 Or dayCount = 6 Then

rstDays.MoveFirst

Do Until rstDays.EOF
If rstDays![Date] = date2 Then
SpecCount = SpecCount + 1
End If

rstDays.MoveNext

Loop

rstDays.MoveFirst

End If

date2 = date2 - 1

' Debug.Print date1, date2, daycount, wkendCount, SpecCount

Loop


'---- Final total of working days ----

If StartDate < EndDate Then
finaldiff = intDiff - (wkendCount + SpecCount)
Else
dbldiff = 2 * (intDiff - (wkendCount + SpecCount))
finaldiff = intDiff - (wkendCount + SpecCount) - dbldiff
End If

End If

Calculate_Working_Days = finaldiff

nulldate = 0

End Function

"John Spencer" wrote:

My G_U_E_S_S is that Calculate_Working_Days is returning an error on one
or more of the records. Probably because you are passing it a null
value in one or more of its arguments and not handling that possibility
in the function.

You might consider posting the function for review.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Allen Browne wrote:
This is a very general message that just means JET got stuck somewhere.

One cause is where the data types don't match. We don't know how your
calculate_working_days() function is declared: what type of arguments
does it take, and what type does it return? Does it accept variants, and
return a variant? What happens if either of the aguments passed in is
null? Is there a chance that JET could understand it as a non-numeric
value, and therefore be unable to evaluate it against a numeric value
(i.e. 10)?


.

Relevant Pages
Loading