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() dtEndDay. If so, then switch the dates
If dtStartDay > dtEndDay Then
dtNominalEndDay = dtStartDay
dtStartDay = dtEndDay
dtEndDay = dtNominalEndDay
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay 0 Then
If Left(strField, 1) "[" Then
strField = "[" & strField & "]"
strCriteria = strField & _
" = #" & Format(TSD, "mm/dd/yyyy") & "#"
If Not rs.NoMatch Then
TSD = TSD + 1
Loop Until rs.NoMatch
Loop Until Not IsWeekend(TSD)
If Status > 1 Then
Status = Status - 1
TSD = TSD + 1
TargetShipDate = TSD
' No matter what the error, just
' return without complaining.
' The worst that could happen is that the code
' includes a holiday as a real day, even if
' it's in the table.
Debug.Print "Error: " & Err & " - " & Error
Private Function IsWeekend(dtStartDate As Date) As Boolean
'test for weekends
Select Case Weekday(dtStartDate)
Case vbSaturday, vbSunday
IsWeekend = True