Why is my function still returning error? (#Error)

[jonathanchye]

I have a function which calculates the Net work days taken from 2 date fields. (Start and End date)

I've ran a query using the function and it looks fine except some fields returning "#Error'
It seems this only applies to records with an empty EndDate field. This is causing me problems as I can't apply a Criteria to this query.

I've modified my function to return specific values in this case but it doesn't seem to work. Below is my function (I hope someone can spot my error?) Thanks.

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Long
    Dim lngDate As Long
    NetWorkdays = -1
    ' Check for valid dates.
    If IsDate(dteStart) And IsDate(dteEnd) Then
        ' Strip off any fractional days and just use whole days.
        For lngDate = Int(dteStart) To Int(dteEnd)
            If Weekday(lngDate, vbMonday) < 6 Then
               If IsNull(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate] = " & lngDate)) Then
                   NetWorkdays = NetWorkdays + 1
               End If
            End If
        Next lngDate
   ' End If
ElseIf IsDate(dteEnd) = False Then NetWorkdays = -1
ElseIf IsEmpty(dteEnd) Then NetWorkdays = -2

End If

End Function

 

[JANR]

2 options.

1. Change dteEnd from Date to Variant and take action in your function to handle Null dates.

2. Use Nz() function in your query to pass an alternative value to your function in case of a missing enddate.

expr: NetWorkdays([StartDate], Nz([EndDate], "some date incase of null"))

JR

 

[jonathanchye]

2 options.

1. Change dteEnd from Date to Variant and take action in your function to handle Null dates.

2. Use Nz() function in your query to pass an alternative value to your function in case of a missing enddate.

expr: NetWorkdays([StartDate], Nz([EndDate], "some date incase of null"))

JR

Thanks. I will have a look at that. In regards to Option # 1, won't my elseif handle that? IE IsDate(dteEnd) = false?

How do I modify my function to return lets say a value of (-1) if dteEnd is null?

It's a bit annoying as I can't set the criteria of the field using this function. It just comes up with Data type mismatch in criteria which I presume are from null values.

 

[JANR]

Public Function NetWorkdays(dteStart As Date, dteEnd As Variant) As Long
Dim lngDate As Long

If IsNull(dteEnd) Then
    Networkdays = -1
    Goto Exitpoint
End If
 
.....Rest of your code
 
Exitpoint:
Exit Function
End Function

JR

 

[jonathanchye]

Just a question though, in my query I've filtered the null dteEnd dates off but why is it still considering null dates? I presume this is because of passing in the "unfiltered" dteEnd into the function? Here is my SQL code :

SELECT Format([EnquiryRec],"mmmm") AS Expr2, tblTrack.EnqNo, tblTrack.DateQuoteSent AS ValSent, NetWorkDays([EnquiryRec],[DateQuoteSent]) AS WorkDays
FROM tblTrack
WHERE (((tblTrack.EnquiryRec) Between #1/1/2011# And #1/31/2011#))
GROUP BY Format([EnquiryRec],"mmmm"), tblTrack.EnqNo, tblTrack.DateQuoteSent, NetWorkDays([EnquiryRec],[DateQuoteSent])
HAVING (((tblTrack.DateQuoteSent) Is Not Null));

 

[jonathanchye]

Thanks mate. I've tried that but I still get "Data type mismatch in criteria expression" when I try to set the Group By critera to let's say 1. It's ok if I leave the criteria field blank.

edit : It seems that everytime I call the NetWorkDays function it queries ALL dates in the table and not the dates I have filtered. Is there a way to pass in the function the dates from the query?

Public Function NetWorkdays(dteStart As Date, dteEnd As Variant) As Long
Dim lngDate As Long

If IsNull(dteEnd) Then
    Networkdays = -1
    Goto Exitpoint
End If
 
.....Rest of your code
 
Exitpoint:
Exit Function
End Function

JR

 

[JANR]

What criteria are you trying to use and on what field?

In totals queries the HAVING clause executes last so that can be a problem, if I were to exclude DateQuoteSent which can be Null I would put it in the Where-clause instead, just untick the "show field" box in the query grid and change GroupBy in the totals row to Where.

JR

 

[jonathanchye]

What criteria are you trying to use and on what field?

In totals queries the HAVING clause executes last so that can be a problem, if I were to exclude DateQuoteSent which can be Null I would put it in the Where-clause instead, just untick the "show field" box in the query grid and change GroupBy in the totals row to Where.

JR

I've tried this earlier but still get "Data type mismatch in criteria" error. I am trying to use criteria "=3" as NetWorkDays function returns a Long value so I want it to only show value in the fields where its 3. It's showing fine without any criteria but get the error when I try to put a criteria in...
This is the SQL bit where I set the criteria :

WHERE (((tblTrack.EnquiryRec) Between #1/1/2011# And #1/31/2011#) AND ((NetWorkDays([EnquiryRec],[DateQuoteSent]))=3))

I think its because of of my dteEnd (DateQuoteSent) fields are empty? They will be empty if the quote is still not sent... anyway here is my latest SQL can you please have a look to see if I done anything wrong?

SELECT Format([EnquiryRec],"mmmm") AS Expr2, tblTrack.EnqNo, tblTrack.DateQuoteSent AS ValSent
FROM tblTrack
WHERE (((tblTrack.EnquiryRec) Between #1/1/2011# And #1/31/2011#) AND ((NetWorkDays([EnquiryRec],[DateQuoteSent]))=3))
GROUP BY Format([EnquiryRec],"mmmm"), tblTrack.EnqNo, tblTrack.DateQuoteSent
HAVING (((tblTrack.DateQuoteSent) Is Not Null));

 

[DCrake]

try using

NetWorkDays([EnquiryRec],Nz([DateQuoteSent],Date()))


Or


NetWorkDays([EnquiryRec],Nz([DateQuoteSent],[EnquiryRec]))

 

[jonathanchye]

try using

NetWorkDays([EnquiryRec],Nz([DateQuoteSent],Date()))


Or


NetWorkDays([EnquiryRec],Nz([DateQuoteSent],[EnquiryRec]))

I've tried that but still getting the Data mismatch error when I try to set any criteria.... what puzzles me is that it shows fine without criteria... It shows numbers like 2, 5, 10 which corresponds to the expected networkdays values...

why can't I set a criteria to filter only results I want? Is it because of using type Long in my function?

This code works fine but won't let me set criteria...

SELECT tblTrack.EnquiryRec, Networkdays([EnquiryRec],[DateQuoteSent]) AS Expr1
FROM tblTrack
WHERE (((tblLitho.EnquiryRec) Between #1/1/2011# And #1/30/2011# And IsNull([DateQuotesent])=False));

 

[JANR]

SELECT tblTrack.EnquiryRec, Networkdays([EnquiryRec],[DateQuoteSent]) AS Expr1
FROM tblTrack
WHERE (((tblLitho.EnquiryRec) Between #1/1/2011# And #1/30/2011# And Not([DateQuotesent]) Is Null));

JR

 

[jonathanchye]

SELECT tblTrack.EnquiryRec, Networkdays([EnquiryRec],[DateQuoteSent]) AS Expr1
FROM tblTrack
WHERE (((tblLitho.EnquiryRec) Between #1/1/2011# And #1/30/2011# And Not([DateQuotesent]) Is Null));

JR

Thank you. That works as well but I can't set criterias. How do I modify your code to only show results from Networkdays = 3?

 

[DCrake]

Sometimes Access does not like you applying a condition to a column that is derived from a caclulation or a function.

Save the query without the condition then create a new query based on this query and apply the filter there.

 

[JANR]

In the criteria row for Expr1 put: 3

JR

 

[jonathanchye]

In the criteria row for Expr1 put: 3

JR

Tried that got Data type mismatch again. I will try to save this query and create another query based on this one. Boy, this would be very tiresome considering eventually I would need a master table summing all the days taken (ie 3 days, 5 days, 8 days etc...)

 

[jonathanchye]

Sometimes Access does not like you applying a condition to a column that is derived from a caclulation or a function.

Save the query without the condition then create a new query based on this query and apply the filter there.

omg... I get the Data type mismatch in criteria again. This my SQL for new query based on query...

SELECT qry2011daystaken1.[Days Taken], qry2011daystaken1.[EnquiryRec], qry2011daystaken1.[DateQuoteSent]
FROM qry2011daystaken1
WHERE (((qry2011daystaken1.[Days Taken])=3));

Is there something wrong with my function? This is how it looks at the moment :

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Long
    Dim lngDate As Long
    NetWorkdays = -1
     If IsNull(dteEnd) Then
     NetWorkdays = -1
     GoTo Exitpoint
     End If
     
    
    ' Check for valid dates.
    If IsDate(dteStart) And IsDate(dteEnd) Then
        ' Strip off any fractional days and just use whole days.
        For lngDate = Int(dteStart) To Int(dteEnd)
        
            If Weekday(lngDate, vbMonday) < 6 Then
               If IsNull(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate] = " & lngDate)) Then
                   NetWorkdays = NetWorkdays + 1
               End If
            End If
        Next lngDate
    End If
    
Exitpoint:
    Exit Function
    
End Function

/scratches head!

 

[DCrake]

This might be an ideal time to look at a demo I posted a while ago. Here is a link to it. This will eliminate the need to use your function.

 

[jonathanchye]

Edit2: I found that if I use the criteria as "1 Or 2 Or 3" it works. Only OR though won't work for any other operators...


I finally got it to work by playing around with grouping a little. However, can anyone tell me how to group all months together (as a sum) ? Here's my SQL

SELECT Format([EnquiryRec],"mmmm") AS [Month], Int(NetWorkdays([EnquiryRec],[DateQuoteSent])) AS [In 3 days], tblTrack.EnqNo
FROM tblTrack
WHERE (((Format([EnquiryRec],"yyyy"))='2011') AND (IsNull([EnquiryRec])=False) AND (IsNull([DateQuoteSent])=False))
GROUP BY Format([EnquiryRec],"mmmm"), Month([EnquiryRec]), Int(NetWorkdays([EnquiryRec],[DateQuoteSent])), tblTrack.EnqNo
HAVING (((Int(NetWorkdays([EnquiryRec],[DateQuoteSent])))=0 Or (Int(NetWorkdays([EnquiryRec],[DateQuoteSent])))=1 Or (Int(NetWorkdays([EnquiryRec],[DateQuoteSent])))=2 Or (Int(NetWorkdays([EnquiryRec],[DateQuoteSent])))=3))
ORDER BY Month([EnquiryRec]);

 

[jonathanchye]

I think I've almost got everything working now. Here is the code to those who are interested. However, I noticed the report based on this query takes a considerable time to load so maybe the design of the query itself can be improved. I wonder if I should index date fields...

TRANSFORM Count(tblTrack.ID) AS CountOfID
SELECT Format([EnquiryRec],"mmmm") AS Expr4
FROM tblTrack
WHERE (((NetWorkdays([EnquiryRec],[DateQuoteSent]))=0 Or (NetWorkdays([EnquiryRec],[DateQuoteSent]))<4) AND ((IsNull([DateQuotesent]))=False) AND ((Format([EnquiryRec],"yyyy"))='2011'))
GROUP BY Month([EnquiryRec]), Format([EnquiryRec],"mmmm")
ORDER BY Month([EnquiryRec])
PIVOT NetWorkdays([EnquiryRec],[DateQuoteSent]);