Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replacing Nulls

Posted on 2001-07-24
14
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 200 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

721 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