Functions Error

[VSolano]

Hi all

I need some guidance regarding this VBA Function.

I created the following function to add deduction based on these parameters. I create some variable with value to test to make sure it works, and it does. The issue is when I tested with real date in a query I was getting an error regarding a null value. Now I do not get any error while running the query, but I got #Error in the amount.

Function DeductionSum(DedEEID As Integer, BenefitID As Integer, StartDate As Date, EndDate As Date)
Dim DAmount As Double
'Dim StartDate As Date
'Dim EndDate As Date
'Dim DedEEID As Integer
'Dim BenefitID As Integer
'DedEEID = 4
'BenefitID = 1
'
'
'StartDate = #1/1/2017#
EndDate = #12/31/2017#

DAmount = (DSum("[deductionamount]", "tbpayrollDeduction", "[DeductionEmployeeID]=" & DedEEID & " AND [DeductionBenefitsID]=" & BenefitID & " AND [payDate]>= #" & Format(StartDate, "mm/dd/yyyy") & "#" & " AND [payDate]<= #" & Format(EndDate, "mm/dd/yyyy") & "#"))

 

[The_Doc_Man]

Where to start...

First, a Function is a typed object that returns a value. Your function has no type associated with it. For example, (and this is merely a guess at what you might really have wanted)...

Function DeductionSum(param, param, ... ) as Currency
...
body of function

DeductionSum = some-currency-value

End Sub

Then, without seeing what your data set looks like, it is hard to know what that selection is trying to do. I might guess the DSum would return up to one year's worth of deductions for a given Benefit ID for a given person if you run this at the end of the year, but less when run in mid-year.

Hint: You can simplify the dates for this specific case:

DAmount = DSum( "[deductionamount]", "tbpayrollDeduction", & _
               "[DeductionEmployeeID]=" & DedEEID & _
        " AND [DeductionBenefitsID]=" & BenefitID & _
        " AND DatePart( 'yyyy', [PayDate] ) = 2017 ) "

This shortcut wouldn't work for ranges that cross year-end boundaries but for the dates you posted, this would work fine.

 

[VSolano]

Thanks for the guide. I was able to fix my issue with your comments