Solved

Replacing Nulls

Posted on 2001-07-24
14
222 Views
Last Modified: 2010-04-06
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.
0
Comment
Question by:omavideniz
14 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312084
if it is only for display,
then you could use the
onGetText, OnSetText-events of the Tfield-object
0
 
LVL 17

Expert Comment

by:geobul
ID: 6312085
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
 

Author Comment

by:omavideniz
ID: 6312109
i have those solutions but i'm looking for if its possible within sql...
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312367
then your database must support a this like function
0
 

Expert Comment

by:meirchen
ID: 6312483
If it is for SQL, then use the NVL(Value,ValueIfNull) function
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312617
NVL is oracle-specific and not applyable to access
NZ is not included in the jet-SQL, it is access-application specific

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312660
NVL is oracle-specific and not applyable to access
NZ is not included in the jet-SQL, it is access-application specific

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312665
oops, double-post :-(
0
 
LVL 2

Expert Comment

by:Felixin
ID: 6312785
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
 
LVL 2

Accepted Solution

by:
Felixin earned 50 total points
ID: 6312820
Being access, you can try writing

SELECT
  F1,
  F2,
  IIF (F3=NULL; 1; F3)
FROM
  TABLE
WEHRE
  ...etc...
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312879
iif works there in a sql-statement? really?
0
 
LVL 2

Expert Comment

by:Felixin
ID: 6313186
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
 
LVL 1

Expert Comment

by:AngelFireMateo
ID: 6315795
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
 

Expert Comment

by:amp072397
ID: 6783784
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now