Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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.
0
omavideniz
Asked:
omavideniz
1 Solution
 
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
 
omavidenizAuthor Commented:
i have those solutions but i'm looking for if its possible within sql...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
FelixinCommented:
Being access, you can try writing

SELECT
  F1,
  F2,
  IIF (F3=NULL; 1; F3)
FROM
  TABLE
WEHRE
  ...etc...
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now