Link to home
Start Free TrialLog in
Avatar of aqk139
aqk139

asked on

Convert Null values into zero

I am using Access 97 database for my VB application. I need to convert null values to zero in my query. I used nz() function in query and it works fine when I run this query in Access but when I use the same  query in VB it gives an error like "Unrecognized function nz". What is the soultion? Is there any other  function that can be use for this purpose? I need to convert null values in query not in my VB coding.

Thanks
Abdul Qadir Khan
Avatar of beercat2
beercat2

Try the IIF function...

IIF(IsNull(Field1),0,Field1)

Something like that would return 0 if Field1 is null, else it will return the contents of Field1.  I'm pretty sure that works with Access.
Be very carefull about the SQL you use with Access.  If you have any thoughts that the application may be scaled up to SQL server you will have huge problems converting all your sql to eliminate the Access 97 bastardized SQL.

This is from experience!  The more general form would be:

Select
  case
    when XXX is null then
         0
     else
         XXX
     end case
      as XXY
           

This should work however you will want to test the code.  By far the comment from beercat2 will be easier, but has problems since SQL server doesn't seem to support the IFF function very well and it doesn't support the isnull() function.
ASKER CERTIFIED SOLUTION
Avatar of Glen Richmond
Glen Richmond
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There has been no activity in this question in quite some time, and it looks like it has been abandoned. As part of our ongoing mission to clean up the topic areas, a Moderator will finalize this question within the next seven (7) days. At that time, either I or one of the other Moderators will force/accept the comment of glenrichmond (simple and to the point).

DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.

aqk139,

Some of these questions have been open for some time, and records indicate you logged in as recently as last week. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20202508
https://www.experts-exchange.com/jsp/qShow.jsp?ta=crystal&qid=20255526
https://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20167212
https://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20165928
https://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20141926
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20243322
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20207867
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20207526
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20191215
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20183720
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20180355
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20175537
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20175003

Thanks,

Netminder
Community Support Moderator
Experts Exchange
Admin notified of user neglect. Force/accepted by

Netminder
Community Support Moderator
Experts Exchange