query field calling a procedure

[nsmith756]

I want to call a module/procedure from a query field to do some simple logic, if statements. I am passing 2 fields from the tables I am joining. I haven't done this for quite a while and it is not working at all. I have googled all over the place without much success. Any help and examples would be greatly appreciated. I will show you what I have done so far after a reply.. because not sure what you want to see.

 

[Galaxiom]

Assuming you want a return value, create a Public function with two parameters in a Standard module.

 

[The_Doc_Man]

First, the module has to be a General Module.
Second, the entry point to your code must be Public.
Third, you must invoke a Function and it will have to return a value.
Fourth, you must pass in the required parameters as formal arguments to the function.

In that context, "side effects" are anywhere from unwise to impossible and likely to be fatal. You CANNOT assume that the context in which your query code is executed is anything like the context in which you developed it. Therefore, the function MUST be self-contained except for what values are passed in as formal arguments and what is passed out as a value. DO NOT attempt to access external Public variables. If you have to open an application object, DEFINITELY remember to close it when done for each call.

 

[arnelgp]

can a simple internal Switch() function on your two fields will do?

 

[Gasman]

Here is an example of mine

SELECT tblTransferFee.TransferFeeID, [Forenames] & " " & [Surname] AS FullName, Jagrate([tblTransferFee.TransferFeeID],[tblClient.IFAID],[TransferAmount]) AS JAGSplit, tblIFA.IFAName, tblTransfer.TransferAmount
FROM ((tblClient INNER JOIN tblIFA ON tblClient.IfaID=tblIFA.IfaID) INNER JOIN tblTransfer ON tblClient.ClientID=tblTransfer.ClientID) INNER JOIN (tblPortfolioRate INNER JOIN tblTransferFee ON tblPortfolioRate.PortfolioRateID=tblTransferFee.PortfolioRateID) ON tblTransfer.TransferID=tblTransferFee.TransferID
WHERE (((tblPortfolioRate.FeeTypeID)=1));

Public Function JAGRate(plngID As Long, plngIFA As Long, pcurTransferAmt As Currency)
' Calculate what % JAG keep of the IFA fee
Dim lngID As Long
Dim blnIsInvestFeePaid As Boolean
lngID = Nz(DLookup("TransferFeeID", "tblSubmitterInvoice", "TransferFeeID = " & plngID), 0)
' if lngID is 0 that means no split of IFA fee as not in submitter table

' Check to see if provider pays Investment Fee Commission
blnIsInvestFeePaid = IsInvestFeePaid(plngID)

Select Case True
    Case (lngID = 0 And plngIFA = 1)
        JAGRate = 0.2
    Case (lngID = 0 And plngIFA = 2)  
        JAGRate = 0.64
    Case (lngID > 0 And plngIFA = 1 And blnIsInvestFeePaid) 
        JAGRate = 0.4
    Case (lngID > 0 And plngIFA = 1 And Not blnIsInvestFeePaid) 
        JAGRate = 0.5
    Case (lngID > 0 And plngIFA = 2 And pcurTransferAmt < 15000) 
        JAGRate = 0.89
    Case (lngID > 0 And plngIFA = 2 And pcurTransferAmt >= 15000) 
        JAGRate = 0.64
End Select
        
End Function

 

[theDBguy]

Can you show us the SQL statement of your query and elaborate a little more on what "not working" means?

 

[nsmith756]

Here is an example of mine

SELECT tblTransferFee.TransferFeeID, [Forenames] & " " & [Surname] AS FullName, Jagrate([tblTransferFee.TransferFeeID],[tblClient.IFAID],[TransferAmount]) AS JAGSplit, tblIFA.IFAName, tblTransfer.TransferAmount
FROM ((tblClient INNER JOIN tblIFA ON tblClient.IfaID=tblIFA.IfaID) INNER JOIN tblTransfer ON tblClient.ClientID=tblTransfer.ClientID) INNER JOIN (tblPortfolioRate INNER JOIN tblTransferFee ON tblPortfolioRate.PortfolioRateID=tblTransferFee.PortfolioRateID) ON tblTransfer.TransferID=tblTransferFee.TransferID
WHERE (((tblPortfolioRate.FeeTypeID)=1));

Public Function JAGRate(plngID As Long, plngIFA As Long, pcurTransferAmt As Currency)
' Calculate what % JAG keep of the IFA fee
Dim lngID As Long
Dim blnIsInvestFeePaid As Boolean
lngID = Nz(DLookup("TransferFeeID", "tblSubmitterInvoice", "TransferFeeID = " & plngID), 0)
' if lngID is 0 that means no split of IFA fee as not in submitter table

' Check to see if provider pays Investment Fee Commission
blnIsInvestFeePaid = IsInvestFeePaid(plngID)

Select Case True
    Case (lngID = 0 And plngIFA = 1)
        JAGRate = 0.2
    Case (lngID = 0 And plngIFA = 2) 
        JAGRate = 0.64
    Case (lngID > 0 And plngIFA = 1 And blnIsInvestFeePaid)
        JAGRate = 0.4
    Case (lngID > 0 And plngIFA = 1 And Not blnIsInvestFeePaid)
        JAGRate = 0.5
    Case (lngID > 0 And plngIFA = 2 And pcurTransferAmt < 15000)
        JAGRate = 0.89
    Case (lngID > 0 And plngIFA = 2 And pcurTransferAmt >= 15000)
        JAGRate = 0.64
End Select
       
End Function

Thank you for the example. The good news I have an error message.. "Undefined function "prodserv" in expression".

 

[Gasman]

Well that is not one of mine, so must be one of yours?
The name in the query and the function must be the same.
Reemember it also has to be decalred Public, as I have done.

 

[cheekybuddha]

The good news I have an error message.. "Undefined function "prodserv" in expression".

I'm not sure where prodserv appears anywhere in your query, but you have got the bracketing wrong on the arguments which you pass to the Jagrate function in your query:

SELECT
  tblTransferFee.TransferFeeID,
  [Forenames] & " " & [Surname] AS FullName,
  Jagrate([tblTransferFee.TransferFeeID],[tblClient.IFAID],[TransferAmount]) AS JAGSplit,
                        ^^^                       ^^^
                        |||                       |||
  tblIFA.IFAName,
  tblTransfer.TransferAmount
FROM ( ...

It should be:

SELECT
  tblTransferFee.TransferFeeID,
  [Forenames] & " " & [Surname] AS FullName,
  Jagrate([tblTransferFee].[TransferFeeID],[tblClient].[IFAID],[TransferAmount]) AS JAGSplit,
  tblIFA.IFAName,
  tblTransfer.TransferAmount
FROM ( ...

or:

SELECT
  tblTransferFee.TransferFeeID,
  [Forenames] & " " & [Surname] AS FullName,
  Jagrate(tblTransferFee.TransferFeeID, tblClient.IFAID, TransferAmount) AS JAGSplit,
  tblIFA.IFAName,
  tblTransfer.TransferAmount
FROM ( ...

 

[Gasman]

@cheekybuddha

It works fine as it is?, though it is no longer used anymore. Data is many years out of date.