Module Function Inside Access SQL Query

[cj92713696]

I created a function in a module and subsequently use the function in SQL Queries I have built in Access. The SQL query works fine inside of Access however when I try to run the query via a front end application I've written in Visual Basic, I get an error message from Access that says the following

"Run-time error '-2147217900 (80040e14)':

Undefined function 'getQuantity' in expression."

Any help is greatly appreciated.

Thanks,
CJ

 

[LittleSmudge]

Because the getQuantity function is outside it's scope at the time you try to call it in the VB app.

Copy the function into the VB app so that VB has access to it and then it will work.


'ope-that-'elps.

G LS

 

[cj92713696]

I created a module inside of my Visual Basic application with the same results. It appears as though Access cannot find the function. I may not be able to call a query from Access via Visual Basic. Any suggestions?

Thanks,
CJ

 

[LittleSmudge]

Oh YES you can - else half of my forms would fall over !


Are you sure that you've defined the Function as PUBLIC ?



G LS

 

[cj92713696]

Here's my entire module pasted exactly. Maybe I don't have my module declared as global if that's even possible?

Module:



Option Compare Database

Option Explicit

Public Function getQuantity(QueryName As String, storeCode As String, ItemCode As String, StartDate As Date, EndDate As Date, Field As String) As Double

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command

Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter

With cmd
Set .ActiveConnection = CurrentProject.Connection

.CommandText = QueryName
.CommandType = adCmdStoredProc

With .Parameters
Set param1 = cmd.CreateParameter("StoreCode", adVarChar, adParamInput, 3, storeCode)
.Append param1

Set param2 = cmd.CreateParameter("ItemCode", adVarChar, adParamInput, 10, ItemCode)
.Append param2

Set param3 = cmd.CreateParameter("StartDate", adDate, adParamInput, , StartDate)
.Append param3

Set param4 = cmd.CreateParameter("EndDate", adDate, adParamInput, , EndDate)
.Append param4
End With

Set rs = .Execute
End With

If rs.RecordCount > 0 Then
getQuantity = Val(rs(Field))
Else
getQuantity = 0
End If

rs.Close
End Function

Public Function getDaiqUsage(storeCode As String, ItemCode As String, StartDate As Date, EndDate As Date, BeginningStartDate As Date, BeginningEndDate As Date) As Double

Dim WasteQuantity As Double
Dim ReceiveQuantity As Double
Dim TransferQuantity As Double
Dim EndInvQuantity As Double
Dim StartInvQuantity As Double

WasteQuantity = getQuantity("A_WasteQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
StartInvQuantity = getQuantity("A_DaiquiriQuantity", storeCode, ItemCode, BeginningStartDate, BeginningEndDate, "SumOfQuantity&quot
EndInvQuantity = getQuantity("A_DaiquiriQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
ReceiveQuantity = getQuantity("A_ReceiveQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
TransferQuantity = getQuantity("A_TransferQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot

getDaiqUsage = StartInvQuantity + ReceiveQuantity - WasteQuantity - TransferQuantity - EndInvQuantity
End Function

Public Function getUsage(storeCode As String, ItemCode As String, StartDate As Date, EndDate As Date, BeginningStartDate As Date, BeginningEndDate As Date) As Double

Dim WasteQuantity As Double
Dim ReceiveQuantity As Double
Dim TransferQuantity As Double
Dim EndInvQuantity As Double
Dim StartInvQuantity As Double

WasteQuantity = getQuantity("A_WasteQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
StartInvQuantity = getQuantity("A_InventoryQuantity", storeCode, ItemCode, BeginningStartDate, BeginningEndDate, "SumOfQuantity&quot
EndInvQuantity = getQuantity("A_InventoryQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
ReceiveQuantity = getQuantity("A_ReceiveQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot
TransferQuantity = getQuantity("A_TransferQuantity", storeCode, ItemCode, StartDate, EndDate, "SumOfQuantity&quot

getUsage = StartInvQuantity + ReceiveQuantity - WasteQuantity - TransferQuantity - EndInvQuantity
End Function

 

[cj92713696]

My query:



PARAMETERS [@StoreCode] Text ( 255 ), [@StartDate] DateTime, [@EndDate] DateTime, [@BeginningStartDate] DateTime, [@BeginningEndDate] DateTime, [@InvCategory] Text ( 255 );
SELECT MasterInventoryItemList.ItemCode AS ItemCode, MasterInventoryItemList.ItemDesc, InventoryCategory.InventoryCategory AS InventoryCat, InventorySubCategory.InventorySubCategory AS InventorySubCat, getQuantity("A_InventoryQuantity",[@StoreCode],[InventoryItemAssoc]![ItemCode],[@BeginningStartDate],[@BeginningEndDate],"SumOfQuantity&quot AS [Begin], getQuantity("A_ReceiveQuantity",[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],"SumOfQuantity&quot AS Receive, getQuantity("A_WasteQuantity",[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],"SumOfQuantity&quot AS Waste, getQuantity("A_TransferQuantity",[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],"SumOfQuantity&quot AS Transfer, getQuantity("A_InventoryQuantity",[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],"SumOfQuantity&quot AS Ending, InventoryItemAssoc.AvgCost AS AvgCost, InventoryItemAssoc.InvUnitOfMeasureKey AS InvUMKey, InventoryItemAssoc.StoreCode, [@StartDate] AS InvStartDate, 0 AS Loc1, 0 AS Loc2
FROM ((InventoryCategory INNER JOIN InventorySubCategory ON InventoryCategory.InventoryCategoryKey = InventorySubCategory.InventoryCategoryKey) INNER JOIN MasterInventoryItemList ON InventorySubCategory.InventorySubCategoryKey = MasterInventoryItemList.InventorySubCategoryKey) INNER JOIN InventoryItemAssoc ON MasterInventoryItemList.ItemCode = InventoryItemAssoc.ItemCode
WHERE (((InventoryItemAssoc.PurUnitOfMeasureKey)<>0) AND ((InventoryItemAssoc.InvUnitOfMeasureKey)<>0) AND ((InventoryItemAssoc.UseUnitOfMeasureKey)<>0))
GROUP BY MasterInventoryItemList.ItemCode, MasterInventoryItemList.ItemDesc, InventoryCategory.InventoryCategory, InventorySubCategory.InventorySubCategory, getQuantity(&quot;A_InventoryQuantity&quot;,[@StoreCode],[InventoryItemAssoc]![ItemCode],[@BeginningStartDate],[@BeginningEndDate],&quot;SumOfQuantity&quot, getQuantity(&quot;A_ReceiveQuantity&quot;,[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],&quot;SumOfQuantity&quot, getQuantity(&quot;A_WasteQuantity&quot;,[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],&quot;SumOfQuantity&quot, getQuantity(&quot;A_TransferQuantity&quot;,[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],&quot;SumOfQuantity&quot, getQuantity(&quot;A_InventoryQuantity&quot;,[@StoreCode],[InventoryItemAssoc]![ItemCode],[@StartDate],[@EndDate],&quot;SumOfQuantity&quot, InventoryItemAssoc.AvgCost, InventoryItemAssoc.InvUnitOfMeasureKey, InventoryItemAssoc.StoreCode, [@StartDate], 0, 0
HAVING (((InventoryCategory.InventoryCategory)=[@InvCategory]) AND ((InventorySubCategory.InventorySubCategory)<>&quot;(Daiquiri) Gallons&quot; And (InventorySubCategory.InventorySubCategory)<>&quot;(Daiquiri) Exotic&quot; And (InventorySubCategory.InventorySubCategory)<>&quot;(Daiquiri) Virgin&quot AND ((InventoryItemAssoc.StoreCode)=[@StoreCode])) OR ((([@InvCategory])=&quot;*&quot)
ORDER BY InventorySubCategory.InventorySubCategory;