Buck Beasom
asked on
VB2005 version of Access "NZ" function
I have a form that loads existing revenue records into text boxes. It tracks 24 months of revenue, so there are 24 boxes (all of this year and next year by month.) Virtually all of the records have some months with zero revenue.
Since the numeric revenue values are displayed as text in the boxes, calculations involving the text values must first convert the text to a number (I'm using Cdbl). It works for the non-zero values but throws an error for the zero values, which come out as "".
I can probably deal with this problem either by creating a parallel double variable array that stores the values, or by separately testing each text box BEFORE converting it - and skipping the zero values - but both options seem pretty clunky.
In Access, this problem was easily solved by using the "NZ" function to convert all Nulls to 0. Is there something similar in VB2005? Or is there another way to get the Cdbl(txtJanRevenue.Text) function to throw a zero when the txtJanRevenue.Text is "$0.00" = which apparently gets read as the null string?
Thanks.
Since the numeric revenue values are displayed as text in the boxes, calculations involving the text values must first convert the text to a number (I'm using Cdbl). It works for the non-zero values but throws an error for the zero values, which come out as "".
I can probably deal with this problem either by creating a parallel double variable array that stores the values, or by separately testing each text box BEFORE converting it - and skipping the zero values - but both options seem pretty clunky.
In Access, this problem was easily solved by using the "NZ" function to convert all Nulls to 0. Is there something similar in VB2005? Or is there another way to get the Cdbl(txtJanRevenue.Text) function to throw a zero when the txtJanRevenue.Text is "$0.00" = which apparently gets read as the null string?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually pretty easy in vb .net:
x contains your type double converted value.
dim x as double
dim num as string
num = ""
System.Double.TryParse(num, x)
x contains your type double converted value.
ASKER
PHF
Great idea. Only one more question:
This line of code:
dblRevenueTotal = CDbl(Nz((Me.txtJanNewCY.Te xt)) + CDbl(Nz((Me.txtFebNewCY.Te xt)) & _
+CDbl(Nz((Me.txtMarNewCY.T ext)) + CDbl(Nz((Me.txtAprNewCY.Te xt)) + CDbl(Nz((Me.txtMayNewCY.Te xt)) & _
+CDbl(Nz((Me.txtJunNewCY.T ext)) + CDbl(Nz((Me.txtJulNewCY.Te xt)) + CDbl(Nz((Me.txtAugNewCY.Te xt)) & _
+CDbl(Nz((Me.txtSepNewCY.T ext)) + CDbl(Nz((Me.txtOctNewCY.Te xt)) + CDbl(Nz((Me.txtNovNewCY.Te xt)) & _
+CDbl(Nz((Me.txtDecNewCY.T ext)) + CDbl(Nz((Me.txtJanNewNY.Te xt)) + CDbl(Nz((Me.txtFebNewNY.Te xt)) & _
+CDbl(Nz((Me.txtMarNewNY.T ext)) + CDbl(Nz((Me.txtAprNewNY.Te xt)) + CDbl(Nz((Me.txtMayNewNY.Te xt)) & _
+CDbl(Nz((Me.txtJunNewNY.T ext)) + CDbl(Nz((Me.txtJulNewNY.Te xt)) + CDbl(Nz((Me.txtAugNewNY.Te xt)) & _
+CDbl(Nz((Me.txtSepNewNY.T ext)) + CDbl(Nz((Me.txtOctNewNY.Te xt)) + CDbl(Nz((Me.txtNovNewNY.Te xt)) & _
+CDbl(Nz((Me.txtDecNewCY.T ext))
Has the final closing paren underlined in blue. I've tried adding another one and removing it, but regardless I get an error.
Great idea. Only one more question:
This line of code:
dblRevenueTotal = CDbl(Nz((Me.txtJanNewCY.Te
+CDbl(Nz((Me.txtMarNewCY.T
+CDbl(Nz((Me.txtJunNewCY.T
+CDbl(Nz((Me.txtSepNewCY.T
+CDbl(Nz((Me.txtDecNewCY.T
+CDbl(Nz((Me.txtMarNewNY.T
+CDbl(Nz((Me.txtJunNewNY.T
+CDbl(Nz((Me.txtSepNewNY.T
+CDbl(Nz((Me.txtDecNewCY.T
Has the final closing paren underlined in blue. I've tried adding another one and removing it, but regardless I get an error.
ASKER
Outstanding idea!
I figured out the paren problem. Need 3 closing parens after every conversion.
Thanks!
I figured out the paren problem. Need 3 closing parens after every conversion.
Thanks!
Looks to me like you are missing 23 closing brackets
This defaults non-convertible strings to 0.0.
It's in C# I'm afraid but the same method is available to VB.Net, although I'm not sure of the exact syntax around out parameters.