Hi! I'm quite new to Access coding, I hope someone can help me with this problem. Below is my function and I keep getting the error "No value given for one or more required parameters".. Hope someone could help me out..
Thank you very much in advance!
Function GetCountryTotal(Country As String) As Double
On Error GoTo Err_handler
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Rs As New ADODB.Recordset
Dim AmountSum As Double
teststring = Country
strquote = Chr$(34)
AmountSum = 0
Set Cn = CurrentProject.Connection
With Cmd
.ActiveConnection = Cn
.CommandText = " SELECT DISTINCT Sum(IIf([TblPledge].[PledgeAmountUSD]=0 Or [TblPledge].[PledgeAmountUSD] Is Null,IIf([TblPledge].[PledgeExchangeRate]>0,[TblPledge].[PledgeAmount]/[TblPledge].[PledgeExchangeRate],0),[TblPledge].[PledgeAmountUSD])) AS USDAmountSum, TblAllotment.AllotmentCountry " _
& " FROM TblPledge INNER JOIN TblAllotment ON TblPledge.PledgeID = TblAllotment.AllotmentPledgeID " _
& " WHERE (((TblPledge.PledgeID) In (select TblPledge.PledgeID from CountryofImplementationQuery))) AND TblAllotment.AllotmentCountry = '" & Country & "' " _
& " GROUP BY TblAllotment.AllotmentCountry"
.CommandType = adCmdText
Set Rs = .Execute
With Rs
.MoveLast
AmountSum = .Fields(0)
End With
.ActiveConnection.Close
End With
GetCountryTotal = AmountSum
Err_handler:
MsgBox Err.Description
Exit Function
End Function
Thank you very much in advance!
Function GetCountryTotal(Country As String) As Double
On Error GoTo Err_handler
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Rs As New ADODB.Recordset
Dim AmountSum As Double
teststring = Country
strquote = Chr$(34)
AmountSum = 0
Set Cn = CurrentProject.Connection
With Cmd
.ActiveConnection = Cn
.CommandText = " SELECT DISTINCT Sum(IIf([TblPledge].[PledgeAmountUSD]=0 Or [TblPledge].[PledgeAmountUSD] Is Null,IIf([TblPledge].[PledgeExchangeRate]>0,[TblPledge].[PledgeAmount]/[TblPledge].[PledgeExchangeRate],0),[TblPledge].[PledgeAmountUSD])) AS USDAmountSum, TblAllotment.AllotmentCountry " _
& " FROM TblPledge INNER JOIN TblAllotment ON TblPledge.PledgeID = TblAllotment.AllotmentPledgeID " _
& " WHERE (((TblPledge.PledgeID) In (select TblPledge.PledgeID from CountryofImplementationQuery))) AND TblAllotment.AllotmentCountry = '" & Country & "' " _
& " GROUP BY TblAllotment.AllotmentCountry"
.CommandType = adCmdText
Set Rs = .Execute
With Rs
.MoveLast
AmountSum = .Fields(0)
End With
.ActiveConnection.Close
End With
GetCountryTotal = AmountSum
Err_handler:
MsgBox Err.Description
Exit Function
End Function