Function in Access

[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

 

[dhookom]

Can you tell us what you are attempting to do and why? I see lots of hard-coded values which is generally not a good idea.

Duane
Hook'D on Access
MS Access MVP

 

[dclardy]

The hard coded values are there for a reason. They never change, and the different functions have been determined based upon those.

Each Log Code has a different way of determining what is SMOG (Slow Moving/Obsolescence Goods). I need the function to find the Log Code and then apply the function to it. The function uses fields in a table that would apply to individual items.

 

[MajP]

you can use the function directly in a query or a form. You need to pass in all of the fields.

Public Function fexcess(LogisticsCode, UOM, CustOrder, SSC, weeklySales, totalInv) As Variant
  Select Case LogisticsCode
    Case "I"
      fexcess = 0
    Case "A", "2", "D", "M", "O", Null
       fexcess = UOM - CustOrder - SSC
   Case "P", "k", "C"
       fexcess = UOM - (weeklySales * 52) - SSC - CustOrder
   Case "K"
       fexcess = totalInv - (weeklySales * 52) - SSC - CustOrder
   Case Null
End Function

 

[MajP]

I think I made a mistake with "K"

Public Function fexcess(LogisticsCode, UOM, CustOrder, SSC, weeklySales) As Variant
  Select Case LogisticsCode
    Case "I"
      fexcess = 0
    Case "A", "2", "D", "M", "O", Null
       fexcess = UOM - CustOrder - SSC
   Case "P", "k", "C"
       fexcess = UOM - (weeklySales * 52) - SSC - CustOrder
      Case Null
End Function

you have lots of repetition. You can do an if statement as

if logisticode = "A" or logisticsCode = "2" ...

 

[MajP]

one more time. Forget the case null

Public Function fexcess(LogisticsCode, UOM, CustOrder, SSC, weeklySales) As Variant
  Select Case LogisticsCode
    Case "I"
      fexcess = 0
    Case "A", "2", "D", "M", "O", Null
       fexcess = UOM - CustOrder - SSC
   Case "P", "k", "C"
       fexcess = UOM - (weeklySales * 52) - SSC - CustOrder
   Case Else
  end select
End Function

 

[dclardy]

Thanks that solved it. You treated log "K" correctly. The repetition is because this was originally in excel. I am moving everything over to access.

Thanks again for the help.

 

[dhookom]

Glad to see this worked for you. I am a bit of a skeptic when it comes to

They never change

As a developer, I have heard that many times before and later found they do change. At least you have the logic in a single, public function rather than in a complex expression in a query or control source.

Duane
Hook'D on Access
MS Access MVP