Convert a null to zero...Nz function

In VB, how can I force a field in a query to give me back a zero???  In Acess, I would say within the query, "select Nz(<fieldName>)...
However, Nz is undefined in VB...
I need to do this within a SQL statement to have the db execute...
i.e. db.Execute strSQLQuery

Any help would be appreciated...
LVL 1
sclaverieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aprasrlCommented:
Why don't you set the DefaultValue for the field to zero ?

Or you could use a statement like this

Select (0+FieldName) As TempField ....
0
sclaverieAuthor Commented:
Sorry, that didn't work...
I also need to be able to add all these Nulls (which will eventually be zeros) together...
Kind of like a spreadsheet...

Anyway, I can't set the DefaultValue to Zero, because I am in a query...I can only set a default value in a table...and I'm making the table dynamically...

I have tried the (0 + fieldName) as well as Val(fieldName)
Neither of these worked...
0
twardCommented:
Use the SQL statement to create a Dynaset:

Dim NewDynaset As Recordset
Dim Total As Long
set NewDynaset = db.OpenRecordset("SELECT FIELDNAME FROM TABLE", dbOpenDynaset)

then you can access the field like this:

Total = val(NewDynaset.fields("FIELDNAME").value & "")

The & "" should take care of a NULL field.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

twardCommented:
Another thing you can also do if you are simply looking for a total:

Dim NewDynaset as Recordset
Dim Total as Long

Set NewDynaset=db.OpenRecordset("SELECT SUM(FIELDNAME) AS TOT_FIELD FROM TABLE", dbOpenDynaset)

Total = NewDynaset.fields("TOT_FIELD").value

This simply makes an alias (TOT_FIELD).
0
sclaverieAuthor Commented:
I already tried converting it to a string, the only problem is that a) it didn't work and b) i need to sum the fields...

Sorry...
0
twardCommented:
That is what the comment was that I added.  This will give you th e sum:

Dim NewDynaset as Recordset
Dim Total as Long

Set NewDynaset=db.OpenRecordset("SELECT SUM(FIELDNAME) AS TOT_FIELD FROM TABLE", dbOpenDynaset)

Total = NewDynaset.fields("TOT_FIELD").value

This simply makes an alias (TOT_FIELD).
0
sclaverieAuthor Commented:
Sorry...
Those two answers didn't work...
I have already tried converting it to a string using the "" and I have already tried the val() function
I also need to be able to add all of these together to get a sum of all values...
Thanx anyway...
0
IWinnerCommented:
What you are asking...cannot be accomplished on the VB side...
If you were returning these items in some sort of reporting tool, like Access or Crystal Reports...then you could set the output to convert the Nulls to Default Value...(In Crystal Reports, go to the Report Options under the file menu and click on the reporting tab...)  That should work...

The only other thing would be to try and do this in Access and make it executable with ADT...but that's a little too expensive of a solution...

Hope that works for ya...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twardCommented:
What I gave you will sum the fields, it is not turning anything into a string...  The second answer I gave (that was a comment at first) will return in a DynaSet the sum of whatever field you tell it to sum....

From your question that is all I understand that you want to do...?

You want to get the sum of a numeric field from a database and put it in a VB variable..?  The second answer I gave is the one that will sum fields for you...

What don't you understand?
0
sclaverieAuthor Commented:
I ended up using the Crystal Report method...

The reason what tward was proposing would not work is because I am averaging numbers first...this is where I get the null, then I want the sum of the averages...what tward proposed would only get rid of the null for the sum, not the averages...
Crystal Reports did the job though...thanx.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.