Solved

VB2005 version of Access "NZ" function

Posted on 2010-11-17
6
304 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:Buck_Beasom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:kraiven
ID: 34154896
Might be better to use Double.TryParse(<string number>, out converted value);
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.
0
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 500 total points
ID: 34154898
You could easily add an implementation of NZ to your solution I would have thought????

i.e.  in a module

Public Function NZ (inputValue as object) as double
  if IsNumeric(inputValue) then
      return CDbl(inputValue)
  else
      return 0
  end if
End Function
0
 
LVL 3

Expert Comment

by:kraiven
ID: 34154940
Actually pretty easy in vb .net:

dim x as double
dim num as string

num = ""

System.Double.TryParse(num, x)

Open in new window


x contains your type double converted value.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

Author Comment

by:Buck_Beasom
ID: 34155032
PHF

Great idea. Only one more question:

This line of code:
dblRevenueTotal = CDbl(Nz((Me.txtJanNewCY.Text)) + CDbl(Nz((Me.txtFebNewCY.Text)) & _
            +CDbl(Nz((Me.txtMarNewCY.Text)) + CDbl(Nz((Me.txtAprNewCY.Text)) + CDbl(Nz((Me.txtMayNewCY.Text)) & _
            +CDbl(Nz((Me.txtJunNewCY.Text)) + CDbl(Nz((Me.txtJulNewCY.Text)) + CDbl(Nz((Me.txtAugNewCY.Text)) & _
            +CDbl(Nz((Me.txtSepNewCY.Text)) + CDbl(Nz((Me.txtOctNewCY.Text)) + CDbl(Nz((Me.txtNovNewCY.Text)) & _
            +CDbl(Nz((Me.txtDecNewCY.Text)) + CDbl(Nz((Me.txtJanNewNY.Text)) + CDbl(Nz((Me.txtFebNewNY.Text)) & _
            +CDbl(Nz((Me.txtMarNewNY.Text)) + CDbl(Nz((Me.txtAprNewNY.Text)) + CDbl(Nz((Me.txtMayNewNY.Text)) & _
            +CDbl(Nz((Me.txtJunNewNY.Text)) + CDbl(Nz((Me.txtJulNewNY.Text)) + CDbl(Nz((Me.txtAugNewNY.Text)) & _
            +CDbl(Nz((Me.txtSepNewNY.Text)) + CDbl(Nz((Me.txtOctNewNY.Text)) + CDbl(Nz((Me.txtNovNewNY.Text)) & _
            +CDbl(Nz((Me.txtDecNewCY.Text))

Has the final closing paren underlined in blue. I've tried adding another one and removing it, but regardless I get an error.


0
 

Author Closing Comment

by:Buck_Beasom
ID: 34155051
Outstanding idea!

I figured out the paren problem. Need 3 closing parens after every conversion.

Thanks!
0
 
LVL 3

Expert Comment

by:kraiven
ID: 34155076
Looks to me like you are missing 23 closing brackets
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question