Slow VBA UDF and error when filtering on a UDF in a query (1 Viewer)

[clobfaz]

Hello,

I have an Access 2010 VBA/Query problem I’d like to see if the forum can address. Basically, I have two problems:

1. My query runs very slowly (over 2.5 hours to pull 2.5M records) since I have 2 custom VBA functions to aid in calculating date ranges. Yes, it’s a lot of records and its growing!
2. The other being that I cannot filter a reports query when the criterion for the filter is applied to a custom VBA function. I get a “Data type mismatch in criteria expression” error.

I'll do my best to give all the relevant information now, apologies if it’s long, I want to be thorough – it’s probably overkill. The gist of the data: ~2.5M records which track line item detail of order shipments. So, multiple lines make an order while a single line is an item in an order. I need to analyze this data and part of that is determining if orders shipped on time, etc. The table has a number of fields but the relevant ones are:

· ORDER NUMBER
· REQUESTED SHIP DATE – (aka the date the order is received)
· DATE ORDER SHIPPED/RETURNED – RETURNED label is irrelevant
· NEW ITEM FLAG – determines if an item is a 90 day exempt item

From these I calculate (Field name – field values: description & formula):

· STATUS – 5, 25 or 90: 5 days if it shipped in 5 days or if 5 days have yet to elapse, 25 days (“backorder”) if it didn’t ship in 5 days or 5 days have already elapsed and 90 days (“exempt”) if the item has an “N” in NEW ITEM FLAG.
o Status: IIf([NEW ITEM FLAG]="N",90,IIf([DATE ORDER SHIPPED/RETURNED] Is Null,IIf((NETWORKDAYS([REQUESTED SHIP DATE],Now()))>5,25,5),IIf((NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))>5,25,5)))
· ACTUAL SHIPPING TIME – Positive Integer: uses UDF NETWORKDAYS to calculate shipping time dependent on whether or not the item has shipped.
o Actual Shipping Time: IIf([DATE ORDER SHIPPED/RETURNED] Is Null,NETWORKDAYS([REQUESTED SHIP DATE],Now()),NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))
· TARGET SHIP DATE – Date: uses UDF NETOWRKDAYS to calculate the day it should’ve shipped accounting for weekends and specific holidays.
o TargetShipDate: TargetShipDate([REQUESTED SHIP DATE],[Status])
· ON TIME – On Time, Late, Pending: analyzes the other calculated fields with DATE ORDER SHIPPED/RETURNED to determine if it was on time, late or pending.
o On Time: IIf(([DATE ORDER SHIPPED/RETURNED] Is Null And Now()<[TargetShipDate]),"Pending",IIf([Actual Shipping Time]<=[Status],"On Time","Late"))

Here's the SQL for the query that the report is based on. This is how it should be (but errors out):

SELECT qryOrders.[ORDER NUMBER], qryOrders.[REQUESTED SHIP DATE], qryOrders.[Actual Shipping Time], Max(qryOrders.[Actual Shipping Time]) AS [MaxOfActual Shipping Time]
FROM qryOrders
WHERE (((qryOrders.Status)=25))
GROUP BY qryOrders.[ORDER NUMBER], qryOrders.[REQUESTED SHIP DATE], qryOrders.[Actual Shipping Time];


BUT, it will run this way, albeit slowly (sans the WHERE caluse):

SELECT qryOrders.[ORDER NUMBER], qryOrders.[REQUESTED SHIP DATE], qryOrders.[Actual Shipping Time], Max(qryOrders.[Actual Shipping Time]) AS [MaxOfActual Shipping Time], qryOrders.Status
FROM qryOrders
GROUP BY qryOrders.[ORDER NUMBER], qryOrders.[REQUESTED SHIP DATE], qryOrders.[Actual Shipping Time], qryOrders.Status;


AND, runs really fast this way (sans the GROUP BY clause):

SELECT qryOrders.[ORDER NUMBER], qryOrders.[REQUESTED SHIP DATE], qryOrders.[Actual Shipping Time], qryOrders.[Actual Shipping Time], qryOrders.Status
FROM qryOrders;


Running it the first way results in a Type Mismatch error. I think this is happens when I try to filter the field “STATUS” by “25” (without quotes since it should be a numeric field) since I'm only looking at Backorders for this report. Removing the “25” as a criteria resolves the issue. I can’t figure out why it thinks STATUS isn’t numeric; the UDF declares it as double and I’ve tried changing it to Long, etc. with the same results. If I use IsNumeric to inspect it all I get is -1 indicating its numeric. I have also run a query on just STATUS and found that the only values present are indeed 5, 25 and 90 – all without spaces or text characters.

Why on earth can’t I filter STATUS by 25? Also, why does it take hours for the query to run? The query runs very quickly when I don’t try to filter a field that uses a UDF and I don’t use the Total Row. The slowness is killing me as I have to do what testing I can and find fixes to various errors and then let it process overnight. Every morning is like Russian roulette as to whether or not it worked or made some other new error :-/

Here is the applicable VBA used in the UDF. I took code from the internet and tweaked it to my need.

[FONT=Arial]Function NetWorkDays(dtStartDay As Date, dtEndDay As Date) As Double[/FONT]
 
[FONT=Arial]'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author[/FONT]
[FONT=Arial]'This function calculates the number of workdays between two dates.[/FONT]
[FONT=Arial]'The number of workdays is inclusive of the beginning and ending dates.[/FONT]
[FONT=Arial]'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time[/FONT]
[FONT=Arial]'The table must list the dates of holidays to be observed.[/FONT]
[FONT=Arial]'The user may include other fields in that table, for example, a description of the holiday being observed.[/FONT]
 
[FONT=Arial]'Function tweaked by Brian Maddocks Feb 2001 to cater for[/FONT]
[FONT=Arial]' 1) The difference in when a week is considered to start between the US and eg the UK ( US = Sunday, UK = monday )[/FONT]
[FONT=Arial]' 2) The fact that the Dcount function ( and indeed all SQL ) can only accept date strings in US format[/FONT]
[FONT=Arial]'The revised function should work fine on both US and UK systems[/FONT]
 
[FONT=Arial]'Function edited by Chris to subtract start date from count[/FONT]
 
[FONT=Arial]Dim lngTotalDays As Long[/FONT]
[FONT=Arial]Dim lngTotalWeeks As Long[/FONT]
[FONT=Arial]Dim dtNominalEndDay As Date[/FONT]
[FONT=Arial]Dim lngTotalHolidays As Long[/FONT]
[FONT=Arial]Dim lngstart As Long[/FONT]
[FONT=Arial]Dim lngend As Long[/FONT]
 
[FONT=Arial]'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates[/FONT]
[FONT=Arial]If dtStartDay > dtEndDay Then[/FONT]
[FONT=Arial]   dtNominalEndDay = dtStartDay[/FONT]
[FONT=Arial]   dtStartDay = dtEndDay[/FONT]
[FONT=Arial]   dtEndDay = dtNominalEndDay[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]'Here are how many weeks are between the two dates[/FONT]
[FONT=Arial]lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)[/FONT]
[FONT=Arial]'Here are the number of weekdays in that total week[/FONT]
[FONT=Arial]lngTotalDays = lngTotalWeeks * 5[/FONT]
[FONT=Arial]'Here is the date that is at the end of that many weeks[/FONT]
[FONT=Arial]dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)[/FONT]
[FONT=Arial]'Now add the number of weekdays between the nominal end day and the actual end day[/FONT]
[FONT=Arial]While dtNominalEndDay <= dtEndDay[/FONT]
[FONT=Arial]   If Weekday(dtNominalEndDay, 2) <> 6 Then[/FONT]
[FONT=Arial]       If Weekday(dtNominalEndDay, 2) <> 7 Then[/FONT]
[FONT=Arial]           lngTotalDays = lngTotalDays + 1[/FONT]
[FONT=Arial]       End If[/FONT]
[FONT=Arial]   End If[/FONT]
[FONT=Arial]   dtNominalEndDay = dtNominalEndDay + 1[/FONT]
[FONT=Arial]Wend[/FONT]
 
[FONT=Arial]'convert end date and startdate into long integer format for the DCount operation to avoid misreading of dates as US format[/FONT]
[FONT=Arial]lngstart = dtStartDay[/FONT]
[FONT=Arial]lngend = dtEndDay[/FONT]
 
[FONT=Arial]'Here are how many holiday days there are between the two days[/FONT]
[FONT=Arial]lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= " & lngend & " AND dtObservedDate >= " & lngstart & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")[/FONT]
 
[FONT=Arial]'Here are how many total days are between the two dates - this is exclusive of the start date and inclusive of the end date[/FONT]
[FONT=Arial]NetWorkDays = lngTotalDays - lngTotalHolidays - 1 'subtract 1 to remove start date from count[/FONT]
 
[FONT=Arial]End Function[/FONT]

[FONT=Arial]Public Function TargetShipDate(dtStartDate As Date, Status As Integer) As Date[/FONT]
 
[FONT=Arial]'TEST OPERATION: pick a combination of dates and number of days to advance such that you start on a holiday/weekend and/or stop on a holiday/weekend.[/FONT]
[FONT=Arial]'e.g. - 2/19/05 and 25 days: starts on a Sa w/ a 2/21/05 holiday then stops on a Sa 3/26/05 moving to a final date of 3/28/05[/FONT]
[FONT=Arial]'e.g. - 9/4/05 and 25 days: starts on a Su w/ a 9/5/05 holiday then stops on Sa 10/8/05 followed by a holiday 10/10/05 moving to final date of 10/11/05[/FONT]
 
[FONT=Arial]'Dim dtStartDate As Date 'start date[/FONT]
[FONT=Arial]Dim TSD As Date 'Target Ship Date[/FONT]
[FONT=Arial]'Dim Status As Integer 'days to advance[/FONT]
[FONT=Arial]Dim rs As Recordset 'table of holidays[/FONT]
[FONT=Arial]Dim strField As String 'field in table of holidays[/FONT]
 
[FONT=Arial]'dtStartDate = #9/4/2005# 'a saturday[/FONT]
[FONT=Arial]'Status = 25[/FONT]
[FONT=Arial]Set rs = CurrentDb().OpenRecordset("tblHolidays", dbOpenDynaset)[/FONT]
[FONT=Arial]strField = "[dtObservedDate]"[/FONT]
[FONT=Arial]Dim strCriteria As String[/FONT]
 
[FONT=Arial]TSD = dtStartDate[/FONT]
 
[FONT=Arial]On Error GoTo ErrHandler[/FONT]
 
[FONT=Arial]TestNextDate:[/FONT]
[FONT=Arial]Do 'advance through weekends[/FONT]
 
[FONT=Arial]   Do Until Not IsWeekend(TSD) 'advance through weekends[/FONT]
[FONT=Arial]       TSD = TSD + 1[/FONT]
[FONT=Arial]   Loop[/FONT]
 
[FONT=Arial]   If Not rs Is Nothing Then 'advance through holidays[/FONT]
[FONT=Arial]       If Len(strField) > 0 Then[/FONT]
[FONT=Arial]           If Left(strField, 1) <> "[" Then[/FONT]
[FONT=Arial]               strField = "[" & strField & "]"[/FONT]
[FONT=Arial]           End If[/FONT]
[FONT=Arial]           Do[/FONT]
[FONT=Arial]               strCriteria = strField & _[/FONT]
[FONT=Arial]               " = #" & Format(TSD, "mm/dd/yyyy") & "#"[/FONT]
 
[FONT=Arial]               rs.FindFirst strCriteria[/FONT]
[FONT=Arial]               If Not rs.NoMatch Then[/FONT]
[FONT=Arial]                   TSD = TSD + 1[/FONT]
[FONT=Arial]               End If[/FONT]
[FONT=Arial]           Loop Until rs.NoMatch[/FONT]
[FONT=Arial]       End If[/FONT]
[FONT=Arial]   End If[/FONT]
[FONT=Arial]Loop Until Not IsWeekend(TSD)[/FONT]
 
[FONT=Arial]If Status > 1 Then[/FONT]
[FONT=Arial]   Status = Status - 1[/FONT]
[FONT=Arial]   TSD = TSD + 1[/FONT]
[FONT=Arial]   GoTo TestNextDate[/FONT]
[FONT=Arial]End If[/FONT]
 
[FONT=Arial]ExitHere:[/FONT]
[FONT=Arial]TargetShipDate = TSD[/FONT]
[FONT=Arial]'Debug.Print TSD[/FONT]
[FONT=Arial]Exit Function[/FONT]
 
[FONT=Arial]ErrHandler:[/FONT]
[FONT=Arial]' No matter what the error, just[/FONT]
[FONT=Arial]' return without complaining.[/FONT]
[FONT=Arial]' The worst that could happen is that the code[/FONT]
[FONT=Arial]' includes a holiday as a real day, even if[/FONT]
[FONT=Arial]' it's in the table.[/FONT]
[FONT=Arial]Debug.Print "Error: " & Err & " - " & Error[/FONT]
[FONT=Arial]Resume ExitHere[/FONT]
[FONT=Arial]End Function[/FONT]

[FONT=Arial]Private Function IsWeekend(dtStartDate As Date) As Boolean[/FONT]
[FONT=Arial]'test for weekends[/FONT]
[FONT=Arial]Select Case Weekday(dtStartDate)[/FONT]
[FONT=Arial]   Case vbSaturday, vbSunday[/FONT]
[FONT=Arial]       IsWeekend = True[/FONT]
[FONT=Arial]End Select[/FONT]
[FONT=Arial]End Function[/FONT]

Thank you,

~Chris

 

[spikepl]

1. Read this http://allenbrowne.com/QueryPerfIssue.html Note the the Group By/First discussion (which may or may not be relevant, I haven't digested your SQL in full)
2. Pay attention to indexes on the relevant fields -ppl often forget to make indexes, and that is the prime suspect in lacking performance
3. Your SQL uses Now() here and there - is that intentional or a goof? Shouldn't it be Date()? It can mess up the right end of date intervals.
4. Dunno why your status criterion is playing up. Try slapping a CLng around it or CInt or whatever. Verify it comes out with a number for all permutations. Or make a UDF - a Select Case would make it more comprehensible.
5. Your second function is a major performance killer. Put the relevant data into an array. Or prepare a table for a simple lookup.

 

[clobfaz]

I edited my original post to make it clearer. I didn’t use technical wording in some areas it would help.

1. I will edit my SQL to use First in the Totals row for REQUESTED SHIP DATE since it shouldn’t repeat and if it did I could just use the first date that occurred. I'm not sure how much time this will save as it seems to run quick w/o the Totals row, using distinct and a criteria. Once any of those are used it grinds to a halt.
2. I'll look into adding indexes
3. I'll use Date() instead of Now(). It calculated correctly with Now() and did so before I got into this mess but Date() seems more correct.
4. I tried slapping a Clng or Cint conversion as a wrapper to the STATUS field’s formula but it didn’t make a difference. I also tried to format the VBA for NetWorkDays at the end of its calculation to ensure a number results but it didn’t make a difference – the error still presented. Are you suggesting I get rid of the nested IIf function’s in favor of a UDF that calculates the STATUS field? I figured several IIf’s would always be faster than an UDF.
5. The second function (TargetShipDate) uses a table (tblHolidays) and VBA looks up holidays from it. I would put it in an array to speed the performance but there are a lot of holidays since this data covers years of history (hence the 2 million plus rows).

 

[spikepl]

Ad 4 . Try a UDFand see - it should be minutes of work. You might save one call to your expensive TargetShipDate, if I understood the code correctly.

Ad 5. Google

working days VBA
or
holidays VBA
.. there is some code out there in some excel forum that operates with arrays. Your UDF opens a recordset each time, and that is very expensive.

Plan B would be to have a table with working days only, so a period of say 10 days would require an index increment of 10 to get the future date. Perhaps your algorithm is more involved - I didn't assimilate it.

 

[spikepl]

Your Status-calculation probably fails. Make a query showing the value of Status. That will likely fail. Check whether the datatypes of the fields used in the Status-calculation are what you assumed.