Function Returns 0

[DS]

I wrote a function but it returns zero. There are definitly records in the
table. Any suggestions on what I might be doing wrong?
Thanks
DS

Public Function SUBEX() As Currency
Dim NOEX As Currency
Dim DOLEX As Currency
Dim PCTEX As Currency

SUBEX = NOEX + DOLEX + PCTEX
End Function

Public Sub NOEX()
Dim NOEX As Currency
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", "tblCheckDetailsTMP",
"CDCheckID = " & Forms!Form1!TxtCheckID & " AND CDDiscountDP = 0"), 0), 2)
End Sub

Public Sub DOLEX()
Dim DOLEX As Currency
DOLEX =
Round(Nz(DSum("(CDQuantity*CDPrice)*(1+[CDTaxRate])+CDDiscountAmount",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("CDQuantity*(CDPrice + CDDiscountAmount)",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Sub

Public Sub PCTEX()
Dim PCTEX As Currency
PCTEX =
Round(Nz(DSum("(CDQuantity*CDPrice)*(1+[CDTaxRate])*CDDiscountPercent",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("(CDQuantity*CDPrice)*CDDiscountPercent",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Sub

 

[Douglas J. Steele]

You've declared NOEX, DOLEX and PCTEX as variables in the function SUBEX,
but you've never defined them. Since the default value for an uninitialized
Currency variable is 0, the function returns the sum 0.00 + 0.00 + 0.00, or
0.00

It's really not clear what you're trying to do. You've declared 3 subs NOEX,
DOLEX and PCTEX, each with a variable with the same name as the sub declared
inside the sub. That means that call sub NOEX, for instance, really doesn't
do anything. It does a calculation, assigns it to variable NOEX and then
ends, meaning that NOEX now has no value, and even if it did, no other
routine would be able to access that value, since it's only declared within
the routine.

I suspect you want to declare NOEX, DOLEX and PCTEX as functions:

Public Function NOEX() As Current
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", "tblCheckDetailsTMP", _
"CDCheckID = " & Forms!Form1!TxtCheckID & " AND CDDiscountDP = 0"), 0),
2)
End Function

Public Function DOLEX() As Currency
DOLEX = _
Round(Nz(DSum("(CDQuantity*CDPrice)* (1+[CDTaxRate])+CDDiscountAmount",
_
"tblCheckDetailsTMP", "CDCheckID = " & _
Forms!Form1!TxtCheckID & " AND CDDiscountDP = 1 AND _
CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("CDQuantity*(CDPrice + CDDiscountAmount)", _
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & _
" AND CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Function

Public Function PCTEX() As Currency
PCTEX = _
Round(Nz(DSum("(CDQuantity*CDPrice)*(1+[CDTaxRate])*CDDiscountPercent",
_
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & _
" AND CDDiscountDP = 1 AND CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("(CDQuantity*CDPrice)*CDDiscountPercent", _
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & _
" AND CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Function

You'd then change SUBEX to

Public Function SUBEX() As Currency

SUBEX = NOEX() + DOLEX() + PCTEX()

End Function

It's critical that you understand the scope of variables. While aimed at
Excel programmers, the explanations at
http://www.ozgrid.com/VBA/variable-scope-lifetime.htm apply equally to
Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I wrote a function but it returns zero. There are definitly records in the
table. Any suggestions on what I might be doing wrong?
Thanks
DS

Public Function SUBEX() As Currency
Dim NOEX As Currency
Dim DOLEX As Currency
Dim PCTEX As Currency

SUBEX = NOEX + DOLEX + PCTEX
End Function

Public Sub NOEX()
Dim NOEX As Currency
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", "tblCheckDetailsTMP",
"CDCheckID = " & Forms!Form1!TxtCheckID & " AND CDDiscountDP = 0"), 0), 2)
End Sub

Public Sub DOLEX()
Dim DOLEX As Currency
DOLEX =
Round(Nz(DSum("(CDQuantity*CDPrice)*(1+[CDTaxRate])+CDDiscountAmount",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("CDQuantity*(CDPrice + CDDiscountAmount)",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Sub

Public Sub PCTEX()
Dim PCTEX As Currency
PCTEX =
Round(Nz(DSum("(CDQuantity*CDPrice)*(1+[CDTaxRate])*CDDiscountPercent",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='A'"), 0), 2) _
+ Round(Nz(DSum("(CDQuantity*CDPrice)*CDDiscountPercent",
"tblCheckDetailsTMP", "CDCheckID = " & Forms!Form1!TxtCheckID & " AND
CDDiscountDP = 1 AND CDDiscountWhere ='B'"), 0), 2)
End Sub

 

[DS]

Thanks Douglas. I changed them all to Functions as well as your other
suggestions and I am now getting a value. I did Subs because somewhere on
the Internet I saw a Function that had Subs do all of the pre-totals and
then the Function added all of those totals up. I can't remember where and
obviously I can't remember how either! Is that barking up a wrong tree, and
if not how would that be done. Once again thank you for all of your help!
DS

 

[Douglas J. Steele]

The only way you can get values back from a sub is if you pass them back and
forth as parameters or you use public variables so I'm not sure I'd
recommend using subs for intermediary calculations.