Create Function inside VBA With multiple variables

[wajidpharmacist007]

Hi, I am new to vba programming and i dont know much basics about vba. I want to make the function that shows the status of purchase order just like this

Function POStatus(VarApprovalStatus, Item_PO_Qty, GRN_Qty)

If VarApprovalStatus = 3 Then
    POStatus = "PO Cancelled"
ElseIf VarApprovalStatus = 2 Then
    POStatus = "PO Rejected"
ElseIf VarApprovalStatus = 1 And GRN_Qty = Item_PO_Qty Then
    POStaus = "Supplies Received"
ElseIf VarApprovalStatus = 1 And GRN_Qty < Item_PO_Qty Then
    POStaus = "Supplies Pending"
ElseIf VarApprovalStatus = 1 And GRN_Qty > Item_PO_Qty Then
    POStaus = "Extra Supply Received"
Else
    POStatus = "Payment Pending"
End If
End Function

I call this code in query column in this way
Purchase Order Status: POStatus([ApprovalStatus],[Item_PO_Qty],[GRN_Qty])
This code works well for first two If statements but for the last three If statement this does not works correctly
Please guide

 

[theDBguy]

Hi. You might try using a Select Case block instead. What values are possible for VarApprovalStatus? 1 through 3 only?

 

[Gasman]

That will be because you cannot spell?

I would also define the actual type of all those parameters passed?

Function POStatus(VarApprovalStatus AS Integer, Item_PO_Qty AS Integer, GRN_Qty AS Integer)

 

[plog]

this does not works correctly

Please be less ambigous-- Error message? Unexpected results? Causes your wife to cheat? No results? Be precise in telling us how you know it doesn't work.

And the answer is--you're missing 3 't's:

POStaus = "Supplies Received"
POStaus = "Supplies Pending"
POStaus = "Extra Supply Received"

 

[wajidpharmacist007]

That will be because you cannot spell?

I would also define the actual type of all those parameters passed?

[QUOTE="Gasman, post: 1778923, member: 96605"]
[QUOTE="Gasman, post: 1778923, member: 96605"]
That will be because you cannot spell? :)

I would also define the actual type of all those parameters passed?

[code]
Function POStatus(VarApprovalStatus AS Integer, Item_PO_Qty AS Integer, GRN_Qty AS Integer)

Thanks Alot by correcting spell mistake and defining code as you share it worked Many many bundle of thanks
[/QUOTE]
[/QUOTE]

 

[theDBguy]

Thanks Alot by correcting spell mistake and defining code as you share it worked Many many bundle of thanks

Hi. Glad to hear you got it sorted out. Make sure you always have Option Explicit at the top of your Modules.

Good luck with your project.

 

[wajidpharmacist007]

Thanks Alot by correcting spell mistake and defining code as you share it worked Many many bundle of thanks

My code is now working well all results are coming upto the mark but while Item_PO_Qty field or GRN_Qty fields are blank then #Error comes. How to get rid of this error.
now my working code is like this

Function POStatus(VarApprovalStatus As Integer, Item_PO_Qty As Integer, GRN_Qty As Integer)

If VarApprovalStatus = 3 Then
    POStatus = "PO Cancelled"
ElseIf VarApprovalStatus = 2 Then
    POStatus = "PO Rejected"
ElseIf VarApprovalStatus = 1 And GRN_Qty = Item_PO_Qty Then
    POStatus = "Supplies Received"
ElseIf VarApprovalStatus = 1 And GRN_Qty < Item_PO_Qty Then
    POStatus = "Supplies Pending"
ElseIf VarApprovalStatus = 1 And GRN_Qty > Item_PO_Qty Then
    POStatus = "Extra Supply Received"
Else
    POStatus = "Payment Pending"
End If
End Function

I know nz function but i dont know where to apply this.

 

[Gasman]

I would not even use the function if any of them do not have a value?, What is the point?

 

[cheekybuddha]

Try it like this:

Function POStatus(VarApprovalStatus As Integer, Item_PO_Qty As Variant, GRN_Qty As Variant) As String

  Dim strRet As String

  Select Case VarApprovalStatus
    Case 1
      Select Case True
        Case IsNull(Item_PO_Qty) = True Or IsNull(GRN_Qty) = True
          strRet = "Missing Data"
        Case GRN_Qty = Item_PO_Qty
          strRet = "Supplies Received"
        Case GRN_Qty < Item_PO_Qty
          strRet = "Supplies Pending"
        Case GRN_Qty > Item_PO_Qty
          strRet = "Extra Supply Received"
      End Select
    Case 2
      strRet = "PO Rejected"
    Case 3
      strRet = "PO Cancelled"
    Case Else
      strRet = "Payment Pending"
  End Select

  POStatus = strRet

End Function

 

[wajidpharmacist007]

Try it like this:

Function POStatus(VarApprovalStatus As Integer, Item_PO_Qty As Variant, GRN_Qty As Variant) As String

  Dim strRet As String

  Select Case VarApprovalStatus
    Case 1
      Select Case True
        Case IsNull(Item_PO_Qty) = True Or IsNull(GRN_Qty) = True
          strRet = "Missing Data"
        Case GRN_Qty = Item_PO_Qty
          strRet = "Supplies Received"
        Case GRN_Qty < Item_PO_Qty
          strRet = "Supplies Pending"
        Case GRN_Qty > Item_PO_Qty
          strRet = "Extra Supply Received"
      End Select
    Case 2
      strRet = "PO Rejected"
    Case 3
      strRet = "PO Cancelled"
    Case Else
      strRet = "Payment Pending"
  End Select

  POStatus = strRet

End Function

Thanks Alot for guidance now every thing is going fine.