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...
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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...
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("FIE
The & "" should take care of a NULL field.
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...
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?
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.
Business Accounts
Answer for Membership
by: aprasrlPosted on 1997-11-19 at 01:31:28ID: 1441406
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 ....