K
KSor
I have made this function in my SQL-DB:
ALTER FUNCTION dbo.KSO_EXL_DKKLandAar
/* Bruges til integration med EXCEL */
(
@Land nvarchar(20),
@Aar int
)
RETURNS money AS
BEGIN
DECLARE @DKK money
SET @DKK =(
SELECT SUM(dbo.CUSTTRANS.AMOUNTCUR * dbo.CUSTTRANS.EXCHRATE
- dbo.CUSTTRANS.VATAMOUNT) AS Oms
FROM dbo.CUSTTRANS INNER JOIN
dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNT =
dbo.CUSTTABLE.ACCOUNT INNER JOIN
dbo.COUNTRY ON dbo.CUSTTABLE.COUNTRY
= dbo.COUNTRY.COUNTRY
WHERE (dbo.CUSTTRANS.DATASET = 'DAT')
AND (dbo.CUSTTABLE.DATASET = 'DAT')
AND (dbo.CUSTTABLE.COUNTRY = @Land)
AND (YEAR(dbo.CUSTTRANS.DATE_) = @Aar)
AND (dbo.CUSTTRANS.TRANSTYPE = 1 OR
dbo.CUSTTRANS.TRANSTYPE = 2)
AND (LEFT(RTRIM(LTRIM(dbo.CUSTTRANS.ACCOUNT)), 1) <> '0')
GROUP BY dbo.COUNTRY.DATASET
HAVING (dbo.COUNTRY.DATASET = 'DAT')
)
RETURN @DKK
END
And I want to call this function from Excel VBA - I tried this:
Function C5_LandAar(Land As String, Aar As Integer) As Double
Dim rs As ADODB.Recordset
Dim strSql As String
strSql = "Select dbo.KSO_EXL_DKKLandAar(" & Land & ", " & Aar & ")"
rs.Open strSql, XXXXXXXXXXXXX
If rs.EOF = False Then
C5_LandAar = rs(0)
End If
rs.Close
Set rs = Nothing
End Function
but nothing comes back to the cell.
What is wrong here ?
ALTER FUNCTION dbo.KSO_EXL_DKKLandAar
/* Bruges til integration med EXCEL */
(
@Land nvarchar(20),
@Aar int
)
RETURNS money AS
BEGIN
DECLARE @DKK money
SET @DKK =(
SELECT SUM(dbo.CUSTTRANS.AMOUNTCUR * dbo.CUSTTRANS.EXCHRATE
- dbo.CUSTTRANS.VATAMOUNT) AS Oms
FROM dbo.CUSTTRANS INNER JOIN
dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNT =
dbo.CUSTTABLE.ACCOUNT INNER JOIN
dbo.COUNTRY ON dbo.CUSTTABLE.COUNTRY
= dbo.COUNTRY.COUNTRY
WHERE (dbo.CUSTTRANS.DATASET = 'DAT')
AND (dbo.CUSTTABLE.DATASET = 'DAT')
AND (dbo.CUSTTABLE.COUNTRY = @Land)
AND (YEAR(dbo.CUSTTRANS.DATE_) = @Aar)
AND (dbo.CUSTTRANS.TRANSTYPE = 1 OR
dbo.CUSTTRANS.TRANSTYPE = 2)
AND (LEFT(RTRIM(LTRIM(dbo.CUSTTRANS.ACCOUNT)), 1) <> '0')
GROUP BY dbo.COUNTRY.DATASET
HAVING (dbo.COUNTRY.DATASET = 'DAT')
)
RETURN @DKK
END
And I want to call this function from Excel VBA - I tried this:
Function C5_LandAar(Land As String, Aar As Integer) As Double
Dim rs As ADODB.Recordset
Dim strSql As String
strSql = "Select dbo.KSO_EXL_DKKLandAar(" & Land & ", " & Aar & ")"
rs.Open strSql, XXXXXXXXXXXXX
If rs.EOF = False Then
C5_LandAar = rs(0)
End If
rs.Close
Set rs = Nothing
End Function
but nothing comes back to the cell.
What is wrong here ?