Member Avatar for dclardy

I am trying to write a function that will perform different calculations based on a Log Code. The function is returning an error of undefined external name. I think that I need to open a recordset, but I do not know how to do this. Any help would be great. The table that I need to pull from is called SMOG Process. The field names are there as well.

Public Function fexcess(LogisticsCode)


If LogisticsCode = "I" Then
fexcess = 0
ElseIf LogisticsCode = "A" Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]
ElseIf LogisticsCode = "2" Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]
ElseIf LogisticsCode = "D" Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]
ElseIf LogisticsCode = "M" Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]
ElseIf LogisticsCode = "O" Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]
ElseIf LogisticsCode = "P" Then
fexcess = [SMOG Process]![Total Inventory UOM] - ([SMOG Process]![12 Month Weekly Sales] * 52) - [SMOG Process]![SSC Allocated] - [SMOG Process]![Customer Orders]
ElseIf LogisticsCode = "K" Then
fexcess = [SMOG Process]![Total Inventory SF] - ([SMOG Process]![12 Month Weekly Sales] * 52) - [SMOG Process]![SSC Allocated] - [SMOG Process]![Customer Orders]
ElseIf LogisticsCode = "C" Then
fexcess = [SMOG Process]![Total Inventory UOM] - ([SMOG Process]![12 Month Weekly Sales] * 52) - [SMOG Process]![SSC Allocated] - [SMOG Process]![Customer Orders]
ElseIf LogisticsCode = Null Then
fexcess = [SMOG Process]![Total Inventory UOM] - [SMOG Process]![Customer Orders] - [SMOG Process]![SSC Allocated]

End If

End Function
  • 2 Contributors
  • 1 Reply
  • 86 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by Difou
Member Avatar for Difou

Here is a function that you can use in a query.
You have to set the parameters like in the sample.

I can't test it but hope it will useful for you

Didier (France)

Public Function fexcess(LogisticsCode, Total_Inventory_UOM, Customer_Orders, SSC_Allocated, Total_Inventory_SF, Month_Weekly_Sales)
'This function can be call in an SQL statment (msaccess only)
'Ex : SELECT *,fexcess([LogicticCode],[Total Inventory UOM],[Customer Orders],[SSC Allocated],[12 Month Weekly Sales],[Total Inventory SF]) AS EXCESS  FROM [SMOG Process]


    Select Case Nz(LogisticsCode, "") 'NZ is used to deal with the Null value
    Case "I"
        fexcess = 0
    Case "A"
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case "2"
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case "D"
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case "M"
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case "O"
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case "P"
        fexcess = Total_Inventory_UOM - (Month_Weekly_Sales * 52) - SSC_Allocated - Customer_Orders
    Case "K"
        fexcess = Total_Inventory_SF - (Month_Weekly_Sales * 52) - SSC_Allocated - Customer_Orders
    Case "C"
        fexcess = Total_Inventory_UOM - (Month_Weekly_Sales * 52) - SSC_Allocated - Customer_Orders
    Case "" 'Null case
        fexcess = Total_Inventory_UOM - Customer_Orders - SSC_Allocated
    Case Else
        fexcess = 0 '  <<<<<<<  Change this
    End Select


End Function
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.