Replacing Nulls

hi,
i'm writing a delphi application with ado that uses ms access database. i want to replace null values that an ado query returns by Nz function but it doesn't recognize Nz function. so what should i use, are there any alternatives for this function.
thanks in advance.
omavidenizAsked:
Who is Participating?
 
FelixinCommented:
Being access, you can try writing

SELECT
  F1,
  F2,
  IIF (F3=NULL; 1; F3)
FROM
  TABLE
WEHRE
  ...etc...
0
 
kretzschmarCommented:
if it is only for display,
then you could use the
onGetText, OnSetText-events of the Tfield-object
0
 
geobulCommented:
Hi,

Just a quick try:

function Nz(var V: Variant; Value: Variant) : Variant;
begin
  if VarIsNull(V) then V := Value;
  result := V;
end;

Regards, Geo
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
omavidenizAuthor Commented:
i have those solutions but i'm looking for if its possible within sql...
0
 
kretzschmarCommented:
then your database must support a this like function
0
 
meirchenCommented:
If it is for SQL, then use the NVL(Value,ValueIfNull) function
0
 
kretzschmarCommented:
NVL is oracle-specific and not applyable to access
NZ is not included in the jet-SQL, it is access-application specific

0
 
kretzschmarCommented:
NVL is oracle-specific and not applyable to access
NZ is not included in the jet-SQL, it is access-application specific

0
 
kretzschmarCommented:
oops, double-post :-(
0
 
FelixinCommented:
If the query is to be read only, then you could make a query in the access database and make your ADO query against the Access query instead of the table.


Other solution, that is part of SQL, is to use the CASE sentence:

SELECT
   F1,
   F2,
   (CASE WHEN F3 IS NULL THEN 0 ELSE F3 END) AS F3
FROM
   TABLE
WEHRE
   ...etc...

in your query text

Most probably, the last solution will make your Ado Query read only.

I'm not that sure.
0
 
kretzschmarCommented:
iif works there in a sql-statement? really?
0
 
FelixinCommented:
Yes, if you're running against an Access database with the BDE configured for Access (driver I mean).

I'm 95% sure of that 'cause we have a project with conditional compilation running for Acces and MS SQL.
0
 
AngelFireMateoCommented:
If you want to know all null record you have to use this:


SELECT FIELD1, FIELD2

FROM TABLE1

WHERE FIELD1 IS NULL


I hope this works


regards

Ricardo
0
 
amp072397Commented:
omavideniz:

You have many open questions:

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20222327
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20261522
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20255833
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20248161
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20247596
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20247451
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20239442
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20235419
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20234355
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20230738
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20227630
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20210091
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20163508
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20163400
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20156064
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20151257
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20149551
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20107807
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20107283
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20096836
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20095000
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20094242
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20081432
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20068029
http://www.experts-exchange.com/jsp/qShow.jsp?qid=12040399
http://www.experts-exchange.com/jsp/qShow.jsp?qid=12040320

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!


thanks!
amp
community support moderator

2/6
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.