Error with Access Stored Query that uses VBA Modules from excel

Question

I am trying to run a Stored MS-access Query from excel using VBA. I have created a generic function to preform this task (using ADODB). I can get results with or without parameters.

However when I try to run a stored Query that references an Access vba module, excel barfs a runtime error "undefined function MissCat" (which is the name of the custom function the stored query uses).

I would have expected this to work as access knows the definition of the function, but now I get the feeling the ADODB interface is trying to interperet the SQL in the stored query without the aid of the VBA module i've created in access.

I am jumping through these hoops as most of my users do not have access installed on their machines.

Is it possible for me to use the stored query as is (changing the query interface, perhaps)? or is the path of least resistance changing the query such that it doesn't need the custom function (as you can see below it is quite simple, I've used it as a crutch because I don't know SQL very well)

here is the function

Function MissCat(ProShip As Date, TargetDate As Date) As String

If TargetDate >= ProShip Then
    MissCat = "Meets target"
    Exit Function
End If

If TargetDate < Date Then
    MissCat = "Unrecoverable"
    Exit Function
End If

Select Case ProShip - TargetDate
    Case 1 To 6
        MissCat = "Less than one week"
    Case 7 To 14
        MissCat = "1-2 Weeks"
    Case Else
        MissCat = "Greater than 2 Weeks"
End Select
End Function

and here is the SQL

TRANSFORM Count(Shipset.ID) AS CountOfID
SELECT Calendar.[Week Of]
FROM Calendar INNER JOIN Shipset ON Calendar.DateSerial = Shipset.ShipDate
WHERE (((Calendar.[Week Of])>Date()-(13*7)) AND ((Shipset.ShipDate) Is Not Null) AND   ((Shipset.ReqOut)=False) AND ((Shipset.TwoTier)=False))
GROUP BY Calendar.[Week Of]
ORDER BY IIf(MissCat([Shipset]![ShipDate],[Shipset]![TargetDate])="Meets Target","Meets Target","Hit") DESC 
PIVOT IIf(MissCat([Shipset]![ShipDate],[Shipset]![TargetDate])="Meets Target","Meets Target","Hit");

thanks,

Access (the application) is not being used when you're using ADO from Excel: that uses the Jet driver, which can't see your Access VBA code. — Tim Williams
– Tim Williams, Commented Sep 21, 2011 at 22:47

onedaywhen · Accepted Answer · 2011-09-22 08:23:16Z

2

Building on @HansUp's excellent analysis, I wonder if a SWICH() statement would be easier to read e.g.

SELECT ProShip,
       TargetDate,
       SWITCH
       (
          TargetDate >= ProShip,      "Meets target",
          TargetDate < Date,          "Unrecoverable", 
          ProShip - TargetDate < 7,   "Less than one week", 
          ProShip - TargetDate < 14,  "1-2 Weeks", 
          TRUE,                       "Greater than 2 Weeks" 
       )
  FROM YourTable;

I also wonder if the using DATEDIFF('D', TargetDate, ProShip) would be easier to read (i.e. decipher intention) than using arithmetic on DATETIME values.

answered Sep 22, 2011 at 8:23

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David-W-Fenton Over a year ago

SWITCH() always raises a red flag for me -- it's a sign of storing data in a SQL statement, and very possible should use a lookup table instead. In this case, it's just a few values, so not such a big deal, but to me a lookup table is better documentation than burying this kind of thing in your SQL.

onedaywhen Over a year ago

David-W-Fenton: "always raises a red flag for me" -- the Stackoverflow-speak for this is "code smell". I think you have a point when the expression is always based on the same predicate but in this case there are multiple cases that makes little sense to use a join for e.g. TargetDate < Date.

HansUp Over a year ago

From the standpoint of including data in the SQL statement, Switch() is no more smelly than the compound IIf() expression I suggested. That IIf() expression stores data just as much as your Switch() example does. But David never raised that complaint against such IIf() approaches; only against Switch(). Pity he's gone so we can't ask him. RIP, David.

HansUp · Accepted Answer · 2011-09-22 00:31:28Z

Your query will not be able to use the custom VBA function except when it is run from within an Access session.

You can substitute nested IIf() function statements for the MissCat function. Two challenges with that approach when the nesting is complex: it's hard to follow the logic; it's easy to screw up the syntax.

The general form is IIf(condition, truepart, falsepart)

So for the first condition, try this in a temporary procedure you create in Access.

Debug.Print IIf(TargetDate >= ProShip, "Meets target", "else")

As the next step, replace "else" with an IIf expression for the second condition.

Debug.Print IIf(TargetDate >= ProShip, "Meets target", _
    IIf(TargetDate < Date, "Unrecoverable", "else"))

And so forth. This is what I wound up with as the final grand mess.

Debug.Print IIf(TargetDate >= ProShip, "Meets target", _
    IIf(TargetDate < Date, "Unrecoverable", _
    IIf(ProShip - TargetDate < 7, "Less than one week", _
    IIf(ProShip - TargetDate < 15, "1-2 Weeks", "Greater than 2 Weeks"))))

After you have the procedure working correctly, copy the nested IIf expression and test it in a new Access query.

SELECT
    ProShip,
    TargetDate,
    IIf(TargetDate >= ProShip, "Meets target", 
    IIf(TargetDate < Date, "Unrecoverable", 
    IIf(ProShip - TargetDate < 7, "Less than one week",
    IIf(ProShip - TargetDate < 15, "1-2 Weeks", "Greater than 2 Weeks")))) AS MissCat 
FROM YourTable;

After looking again at how you used the MissCat function in your original query, I don't think I'd try to substitute this approach directly. I would create a separate query to transform the base data and then adapt the original query to use the new query as one of its data sources.

Collectives™ on Stack Overflow

Error with Access Stored Query that uses VBA Modules from excel

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related