如何使公共函数基于当前月份在MS Access 2016中返回SUM值 [英] How to make a Public Function return SUM value based off current Month in ms access 2016

问题描述

我有我要使用的这段代码，但是当查询调用它时，它将返回一个空白的数据列.因此，我正在尝试在每列中添加数字值，并每月使用数学公式添加一个新列.

I have this code that i am trying to use however when the query calls it it returns a blank column of data. so on it i am trying to add the number value in each column and adding a new column each month with the math formula.

Option Explicit

Public Function FCalcMnthALLTDISTRIB()
Dim OCT As String
Dim NOV As String
Dim DEC As String
Dim JAN As String
Dim FEB As String
Dim MAR As String
Dim APR As String
Dim MAY As String
Dim JUN As String
Dim JUL As String
Dim AUG As String
Dim SEP As String

OCT = Nz([OCT], 0)
NOV = Nz([OCT], 0) + Nz([NOV], 0)
DEC = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0)
JAN = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0)
FEB = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0)
MAR = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0)
APR = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0)
MAY = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0) + Nz([MAY], 0)
JUN = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0) + Nz([MAY], 0) + Nz([JUN], 0)
JUL = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0) + Nz([MAY], 0) + Nz([JUN], 0) + Nz([JUL], 0)
AUG = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0) + Nz([MAY], 0) + Nz([JUN], 0) + Nz([JUL], 0) + Nz([AUG], 0)
SEP = Nz([OCT], 0) + Nz([NOV], 0) + Nz([DEC], 0) + Nz([JAN], 0) + Nz([FEB], 0) + Nz([MAR], 0) + Nz([APR], 0) + Nz([MAY], 0) + Nz([JUN], 0) + Nz([JUL], 0) + Nz([AUG], 0) + Nz([SEP], 0)


If MonthName(Month(Date), False) = "October" Then
    FCalcMnthALLTDISTRIB = OCT
    ElseIf MonthName(Month(Date), False) = "November" Then
        FCalcMnthALLTDISTRIB = NOV
        ElseIf MonthName(Month(Date), False) = "December" Then
            FCalcMnthALLTDISTRIB = DEC
            ElseIf MonthName(Month(Date), False) = "January" Then
                FCalcMnthALLTDISTRIB = JAN
                ElseIf MonthName(Month(Date), False) = "February" Then
                    FCalcMnthALLTDISTRIB = FEB
                    ElseIf MonthName(Month(Date), False) = "March" Then
                        FCalcMnthALLTDISTRIB = MAR
                        ElseIf MonthName(Month(Date), False) = "April" Then
                            FCalcMnthALLTDISTRIB = APR
                            ElseIf MonthName(Month(Date), False) = "May" Then
                                FCalcMnthALLTDISTRIB = MAY
                                ElseIf MonthName(Month(Date), False) = "June" Then
                                    FCalcMnthALLTDISTRIB = JUN
                                    ElseIf MonthName(Month(Date), False) = "July" Then
                                        FCalcMnthALLTDISTRIB = JUL
                                        ElseIf MonthName(Month(Date), False) = "August" Then
                                            FCalcMnthALLTDISTRIB = AUG
                                            ElseIf MonthName(Month(Date), False) = "September" Then
                                                FCalcMnthALLTDISTRIB = SEP

End If

End Function

我正在从查询表达式中调用此函数，并在查询中创建自己的字段.因此，在查询设计视图的空白字段中，我输入了 OB_PLAN:FCalcMnthALLTDISTRIB().然后在[分组依据]中选择 SUM .不知道我要去哪里错了.

I am calling this function from an expression in the query with it creating its own field in the query. So in the query design view in a blank field i put in OB_PLAN: FCalcMnthALLTDISTRIB(). then in the Group By i selected SUM. not sure where i am going wrong.

推荐答案

如果您希望通用模块中的函数处理表中的数据并返回值以进行查询，则需要通过参数将条件传递给函数并打开一个数据记录集.像这样:

If you want a function in a general module to process data from a table and return value to query, then need to pass criteria to function via argument and open a recordset of data. Something like:

Public Function FCalcMnthALLTDISTRIB(intID As Integer) As Long
Dim dblOCT As Double
Dim dblNOV As Double
Dim dblDEC As Double
Dim dblJAN As Double
Dim dblFEB As Double
Dim dblMAR As Double
Dim dblAPR As Double
Dim dblMAY As Double
Dim dblJUN As Double
Dim dblJUL As Double
Dim dblAUG As Double
Dim dblSEP As Double
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tablename WHERE ID=" & intID)

With rs
    dblOCT = Nz(!OCT, 0)
    dblNOV = Nz(!OCT, 0) + Nz(!NOV, 0)
    dblDEC = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0)
    dblJAN = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0)
    dblFEB = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0)
    dblMAR = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0)
    dblAPR = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0)
    dblMAY = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0) + Nz(!MAY, 0)
    dblJUN = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0) + Nz(!MAY, 0) + Nz(!JUN, 0)
    dblJUL = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0) + Nz(!MAY, 0) + Nz(!JUN, 0) + Nz(!JUL, 0)
    dblAUG = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0) + Nz(!MAY, 0) + Nz(!JUN, 0) + Nz(!JUL, 0) + Nz(!AUG, 0)
    dblSEP = Nz(!OCT, 0) + Nz(!NOV, 0) + Nz(!DEC, 0) + Nz(!JAN, 0) + Nz(!FEB, 0) + Nz(!MAR, 0) + Nz(!APR, 0) + Nz(!MAY, 0) + Nz(!JUN, 0) + Nz(!JUL, 0) + Nz(!AUG, 0) + Nz(!SEP, 0)
End With

Select Case Month(Date))
    Case 10
        FCalcMnthALLTDISTRIB = dblOCT
    Case 11
        FCalcMnthALLTDISTRIB = dblNOV
    Case 12
        FCalcMnthALLTDISTRIB = dblDEC
    Case 1
        FCalcMnthALLTDISTRIB = dblJAN
    Case 2
        FCalcMnthALLTDISTRIB = dblFEB
    Case 3
        FCalcMnthALLTDISTRIB = dblMAR
    Case 4
        FCalcMnthALLTDISTRIB = dblAPR
    Case 5
        FCalcMnthALLTDISTRIB = dblMAY
    Case 6
        FCalcMnthALLTDISTRIB = dblJUN
    Case 7
        FCalcMnthALLTDISTRIB = dblJUL
    Case 8
        FCalcMnthALLTDISTRIB = dblAUG
    Case 9
        FCalcMnthALLTDISTRIB = dblSEP
End Select
End Function

查询中的调用函数，例如: SELECT tablename.*, FCalcMnthALLTDISTRIB([ID]) AS OB_PLAN FROM tablename;

Call function in query like: SELECT tablename.*, FCalcMnthALLTDISTRIB([ID]) AS OB_PLAN FROM tablename;

这篇关于如何使公共函数基于当前月份在MS Access 2016中返回SUM值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！