can someon help me how to put default zero (0) when no data is found..

[edgarbenilde]

i want to count of all entries inside specefic date i got it done my problem is i want to put an aumaticay 0 (zero) when no data is found... the source data is from the table i did this querry in an data querry.. the reason why i want to put zero because this field call by the other querry also which is need for mathematical calculation when the selection has no data found it's totally blank the result is the other compution get error because there is no number found.. that is why i want put zero (0) when there no data found..

please help..
my database is access 2003
here is my code

SELECT DISTINCT (mydate)
FROM ttmcall
WHERE mymonth='12';

[VBFnewcomer]

what exactly do you mean by blanks a NULL or empty data both are diff.

[TysonLPrice]

i want to count of all entries inside specefic date i got it done my problem is i want to put an aumaticay 0 (zero) when no data is found... the source data is from the table i did this querry in an data querry.. the reason why i want to put zero because this field call by the other querry also which is need for mathematical calculation when the selection has no data found it's totally blank the result is the other compution get error because there is no number found.. that is why i want put zero (0) when there no data found..

please help..
my database is access 2003
here is my code

SELECT DISTINCT (mydate)
FROM ttmcall
WHERE mymonth='12';

I'm not quite sure what you are asking but in SQL 2000 something like this might be it. Maybe Access 2003 has an similar function

Create table #Temp(Something char(1))

--retuens zero because nothing was found
if not exists(select Something from #Temp) select 0
    else select something from #Temp	

-- put something in there
insert into #temp(something) values('X')

-- found something and it returns it
if not exists(select Something from #Temp) select 0
    else select something from #Temp	

drop table #temp

[edgarbenilde]

i mean i have data already in some month of the selected table..
see the codes below

SELECT DISTINCT (mydate) as Sept
FROM ttmcall
WHERE mymonth='09';

this is the code that i have use to display those dates the used in the selected month.. this code work when the month is used and have a transaction saved in the table... it work fine, now the problem is that if the month have selected has no transaction happen (totally blank for the month) because the field is null as in no display.. how could i put is month selected has no transaction record the value of (mydate as Sept) will be aumatically become .01 not zero because it give me error when it's zero..

LOOK AT THE SAMPLE BELOW IF HAS RECORD THE RECORD DISPLAY CAN EASILY COUNT ON IT BUT IF THERE IS NO DATA IT'S NOTHING TO COUND.
I WANT TO PUT
.01 IF THERE IS NO TRANSACTION HAPPEN..

[edgarbenilde]

i tried an alternative for my problem here in Formula field in crystal report may be this is the solution...
but i can't make it realy work..

can someone correct my codes please?
if the TsWorkDays.Feb is null or nothing like sample image above in "Aug"it will generate "0" value

(If IsNull({TsWorkDays.Feb}) Then
"0"
Else
  {TsWorkDays.Feb}
)

[RunsWithScissors]

As long as you do this from within Access, you can use the nz Function:

SELECT Nz([tblOrders]![OrderDate],.01) AS OrderDAte
FROM tblOrders;

Otherwise, if you are handling the Data Access from Visual Studio/DataSet/DataTable/DataReader what have you, you can use DbNull The handle Null Returns. Visual Studio does not know about the Nz Function, and the OleDB provider apparently doesn;t either.

Can you show us the ENTIRE procudeure you are using to pull the data?

[edgarbenilde]

how to use dbnull? i'm using access querry for me to select access table/s.. then from that access table or querry when i to get the result i wanted to.. i just simply connect it to crystal report and drag the field to the window of the report..

[RunsWithScissors]

If you are using the native access query then you should be OK using the Nz(0 Function within Access.

To use DbNull in vs Code, check out this example (Very simple . . . sends output to the Output window):

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strSELECT As String = "" & _
                "SELECT OrderDate " & _
                "FROM tblOrders"

        Using cn As New OleDbConnection(My.Settings.cxnAcForumDemos)
            Using cmd As New OleDbCommand(strSELECT, cn)

                Dim dr As OleDbDataReader
                cn.Open()
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                While dr.Read
                    'THe  DbNull FUnction:
                    If Not IsDBNull(dr("OrderDate")) Then
                        'For this example, outputs either a date value:
                        Console.WriteLine(dr("OrderDate"))
                    Else
                        'or if the DataReader returns a NULL, then:
                        Console.WriteLine(0.01)
                    End If
                End While
            End Using
        End Using
    End Sub

No rocket science here, but you get the idea . . .

However, as long as you are running your query within access, which is what is sounds like to me, the Nz() function will be easier.

[edgarbenilde]

i having harder in ur sample because the out of what i wanted to do is to put into the crystal report..

my sample is the best solution one of the best.. but the code is not work yet.. can iu please make the code work in logic of my code

(If IsNull({TsWorkDays.Feb}) Then
   If {TsWorkDays.Feb} In [>"0"] Then
   TsWorkDays.Feb
Else
    "0"
Else
    "0"
)
 
this code is under formula field of crystal report

[RunsWithScissors]

I will ask again; can you post your ENTIRE Code?

From where I sit, I have NO IDEA what the problem is with your logic structure. What you are showing me is an If/Then/Else Structure. From a logical standpoint, it looks fine.

What is the object tsWorkdays? How did you populate it? How did you load the data in the first place?

We can't provide much help based on what you are showing us, beyond what has already been suggested.

Most important, what do you mean when you say:

but the code is not work yet.. can iu please make the code work in logic of my code

What does not work? Are you getting an exception? incorrect results?????

Please help us help you by providing enough information.

[RunsWithScissors]

i having harder in ur sample because the out of what i wanted to do is to put into the crystal report..

I don't know much about Crystal, but to my way of thinking, data validation, null handling, and such should PROBABLY be handled BEFORE the report-generation phase of things, where possible. It seems that handling a null substitution at the report field level si a work-around that should hasve you looking at either your query structure, your table structure, or your da structure. Having said that, there are also times when it is just plain unavoidable, but I don't think this is one of those times . . .

I try to think of the report as just that- reporting results.

While it is necessary to place some calculation logic in a reprt, I would try to address your null value concerns either at the Database end, or in the routine which accesses the database and populates your report datasource.

But that's just me . . .

[RunsWithScissors]

Last point-

If we were able toi see your entire code (as well as the SQL you are using to query access), we might have a better idea of what you are trying to do. I say this because I am questioning your use of SELECT DISTINCT, and whether this is the SQL you want to be using. My understanding is that for most cases, SELECT DISTINCT is not optimal. I CAN say that I have never used it, and have yet to run into the situation where I though it might be an answer. Of course, having avoided it for several years, I might be missing something.

[edgarbenilde]

i tried to compile those i tis' too many.. because for every cell of the report (like excel every cell has it's own formula of coputation) has it's own computation... and it's 4 pages report

[edgarbenilde]

Run, i have tried the Nz function but its not give me result.. the field has no data found..
how could i put a condition ex.
[mymonth="1"] then is there is no data found the .01 will execute or will be the default value when there is no data found...

how?

SELECT Nz([TTMcall]![mymonth],.01) AS OrderDAte
FROM TTMcall;

[RunsWithScissors]

Are you running this as a query in Access itself, or are you trying to execute it from code in Visual Studio?

This will only work within Access itself, as vs Does NOT understand the Nz Function.

DO this:

Go to Access. Go to the "Queries" window, and double-click on the new query icon. Then, when the "Add Table" window pops up, hit "Cancel".

NOW, go to Menu "View/SQL View".

Clear the window and past the following in:

SELECT Nz([TTMcall]![mymonth],.01) AS MyDataField
FROM TTMcall;

Then hit the red Exclamation point button, and tell me what happens.

In this, I am assuming that your field mymonth is the field which may return null values.

[edgarbenilde]

yes i did it inside access querry i am using access 2003..
yes the code is work fine.. but the is no data found in the field.. it means it still null... what i want to do here is if the field is null i want to put it .01

[RunsWithScissors]

The function as described should do the trick. It should be returning .01 for any record in which the mymonth feild in the query return is null.

Is there any chance your query is returning a zero length string instead of Null (they are NOT the same thing)? THis would happen if you ever SAVED a zero-length string INTO the field in the parent table. In this case, the Nz function does NOT consider the field NUll, it considers it to be a Zero-length string, and will return exacly that.

What ARE you using Text values to work with what amounts to integers? Changing the datatype of the field in the table to Number, and eliminating all the text-based stuff would solve this problem for you . . .

When I run an identical query on my machine w/Access 20-03, against a table where the column mymonth is a text field with some months enumerated like so:

"01", "02", etc.

and Some others left NULL (No data), my query returns ".01" for any record in which mymonth is null.

I think you are going to have to be more clear as to what you are trying to do.

What type of data are you trying to return? If you can't provide a better idea of the rest of your code or project, I am afraid I can't be of much more assistance. We are going in circles here, and I suspect that the problem is one of communication.

You have a field in a query return which may include some null values. I have shown you a function within access which will handle the null values. Since I have no idea what you are doing witht he data from here, I can't tell where the problem might be.

[edgarbenilde]

ah ok.. i got ur sample now i just change the data type into number then the other month that is null has become .01 in your sample, some of the field has data in the same line but some line of the mymonth field you didn't put data..
sample

ID           Name               mymonth
1            ABCD                  01
2            bcde                  02
3            cccc                  
4            acaca                 03
5            aaabb                 04
6            bbbb

in sample two of them will become .01
the line 3 and 6..

in ur case yes it's correct it work
but i'm not doing it like that..
what i mean null or blank is..
for ex. in above writen
i want to select mydate where mymonth="09"
the result is really blank because there is no data for mymonth=09
that is what i want to be have automatically become .01

[edgarbenilde]

Runs, thanks we are now near to the solution.. when i make the data type into number the nz function work with .01 but when it is text data type it doesn't work.. how could i make it work even if the data type is text.. because i will change it to number many done field will also change and some will may result error in the future like for example if i put in the querry in access mymonth='01' if i able to use ( ' ) symbol in some of my done report.. i'm sure it will make error because as i know (mymonth='01' ) will only work with text data type if it is number it should be this one (mymonth="01 ")

[RunsWithScissors]

Try this.

A. Open Access, open the VBA IDE, and Insert a new module (Not a class module, but a plain old public module).

B. Paste THIS Code into the new module:

Public Function ExtendNz(ByVal Value As Variant, ByVal ValueIfNull As Variant) As Variant
    Dim ReturnValue As Variant

 
    'First check for plain old null values:
    If Not IsNull(Value) Then
        'Then check to see if this is a numeric value:
        If Not IsNumeric(Value) Then

            If it is NOT, check to see if it might be a zero-length String:
            If Len(Value) > 0 Then
                
                'The length was greater than zero, so use the
                'value passed in:
                ReturnValue = Value
            Else
                'The Variant passed in was not null, bit the length
                'was not greater than zero. Use the "Value If Null":
                ReturnValue = ValueIfNull
            End If
        Else
            'A numeric value was passed in. Use as-is:
            ReturnValue = Value
        End If
    Else
        'A real null value was passed in. Use "ValueIfNull":
        ReturnValue = ValueIfNull
    End If
        
    'Return the appropriate value to the calling method:
    ExtendNz = ReturnValue
    
End Function

THEN

Open your Query and use THIS function instead of nz:

SELECT ExtendNz([TTMcall]![mymonth],.01) AS MyDataField
FROM TTMcall;

THIS version will test for zero-length strings, and handle them as if they are null values. You can pass numbers or text values in.

Be aware:

A. Code in Access/VBA is a little different than .NET

B. I have not tested this exhaustively; in fact, I made it up (mostly) right now.

C. This is a poor work-around for properly set up table and data definitions. In the future, just because correcting a poor implementation might be "Hard" or might cause you to have to re-do some other code which depends upon the poorly implemented piece, is no reason not to FIX IT. ;-).

D. Let me know if this works . . .

[edgarbenilde]

it gives me error undefined function(ExtendNz in expression)

[RunsWithScissors]

You have to save the module after you paste the code into it.

If you pasted the code into the modeule, and then just closed the code window and modified your query, it won't see the new code yet.

After you paste the new code into the module, close the code window, then close Access. It SHOULD ask if you want to save changes to module1. Say "Yes".

Then re-open access and modify your query.

[UtOkbOlinAO]

NOTE:
When we say NULL, there is a data in it but it's data is = NULL

When your query returns no row it means that there are no data on it... It's data is simply not NULL..

[RunsWithScissors]

NOTE:
When we say NULL, there is a data in it but it's data is = NULL

When your query returns no row it means that there are no data on it... It's data is simply not NULL..

A. NULL, in the programming sense, means "Absense of Data" or "Unknown". A query which contains NULL values in a field against which one is setting criteria will exclude rows which contain NULL, for obvious reasons. A query with a criteria of "is null" set against a field which contains NULL values will return only NULL values, meaning "WHERE the value of this field is not known".

However, A Criteria of NULL set agains a filed which contains zero-lenth strings will not return record in which the field contains zero-length strings. THis can happen when a procedure sets a string variable to "" (Often as a means of resetting a textbox or other control to show as "Empty" on a form), and then that "" value is saved into the database during an INSERT or UPDATE. The value of that field will no longer be NULL, and will not be handled as NULL by a function such as the Access-native Nz, or any other process which seeks to handle only NULL values. Hence, the following:

Public Function ExtendNz(ByVal Value As Variant, ByVal ValueIfNull As Variant) As Variant
    Dim ReturnValue As Variant

 
    'First check for plain old null values:
    If Not IsNull(Value) Then
        'Then check to see if this is a numeric value:
        If Not IsNumeric(Value) Then

            'If it is NOT, check to see if it might be a zero-length String:
            If Len(Value) > 0 Then
                
                'The length was greater than zero, so use the
                'value passed in:
                ReturnValue = Value
            Else
                'The Variant passed in was not null, bit the length
                'was not greater than zero. Use the "Value If Null":
                ReturnValue = ValueIfNull
            End If        Else
            'A numeric value was passed in. Use as-is:
            ReturnValue = Value
        End If
    Else
        'A real null value was passed in. Use "ValueIfNull":
        ReturnValue = ValueIfNull
    End If
        
    'Return the appropriate value to the calling method:
    ExtendNz = ReturnValue
    
End Function

B. The OP's problem (I think-it is difficult to understand some of what he is trying to say due to language barriers I think) is NOT that he is not returning a row. He is getting a row return with an empty field, which MAY be null, or MAY contain an empty string (This is my THEORY, anyway; again, somewhat hard to tell . . .).

The other possibility is that the empty return is the result of an OUTER JOIN somewhere in the op's code or SQL, although again, without knowing a little more about the op's code, it is hard to tell . . .

[TysonLPrice]

NOTE:
When we say NULL, there is a data in it but it's data is = NULL

When your query returns no row it means that there are no data on it... It's data is simply not NULL..

I didn't think there is any data involved with NULL at all...There is an indicator in the data base indicating a lack of data.

[RunsWithScissors]

You are correct. NULL (by my understanding) means "We don't know".

I will use an example from a book, "Professional SQL Server 2005 Programming, by Robert Vieria:

Values that are indeterminate are said to be null . . . By definition, a NULL value means you don't know what the value is. It could be 1, it could be 347, it could be -294. In short, it means undefined or perhaps not applicable.

For my own purposes, I think it is important to distinguish the relative nature of null as well. A null value is undefined from the DATABASE'S perspective. YOU may know what the value is, but until the database knows, a null value is NOT the same as zero, it means the database does NOT KNOW.

This is why you can't use equivelency with null values as well. To say <Null Value> = <Null value> is to say this Unknown value is EQUAL TO that unknown value, which is an impossible statement.

NOTE: "Borrowed" and paraphrased example from same book:
Imagine a company which brings a database on-line at the beginning of the year. One of the reports this database offers is a comparison of current year performance with the previous years performs for sales. NOw lets say that part of this comparison is on a field representing the sales of Widgets for each year. Since the database has NO DATA for widget sales for the previous year, no comparison can be made. Substitution of a zero value is not valid either, because the previous year's sales we PROBABLY not zero (or there wouldn't be a new database!). If this comparison is made with, say, and OUTER JOIN of some type, the fields returned for the previous year will be (Yep) NULL.

THIS is ALSO why one must be careful using substitutions for NULL. In the OP's case, he really is trying to account for the fact that there were no transactions within a specigied period (I think . . .). He is also probably trying to further avoid a 'Divide-by-zero" type error by returning .01 instead of zero in place of the null (I am guessing here, but it would make sense- to a point).

[UtOkbOlinAO]

Try this pseudocode:

if rst.eof then
<VARIABLE> = .01
else
<VARIABLE> = <COLUMN NAME OF THE RETURNED QUERY>
end if

i think this is the pseudocode that the TS wants...

[TysonLPrice]

A null value is undefined from the DATABASE'S perspective. YOU may know what the value is, but until the database knows, a null value is NOT the same as zero, it means the database does NOT KNOW.

That's not is what is happening behind the scenes in SQL 2005. There is a bit switch indicating there is no value. That's what the database "knows". It's not that there is a column or some data field without a value. From a programming standpoint I understand what you mean though in your posts.

This is why you can't use equivelency with null values as well. To say <Null Value> = <Null value> is to say this Unknown value is EQUAL TO that unknown value, which is an impossible statement.

That's not exactly true either. You can explicity set a value to NULL and then compare NULL = NULL. Try this:

SET ANSI_NULLS OFF
DECLARE @val CHAR(4)
SET @val = NULL
If @val = NULL print 'True'

[RunsWithScissors]

TysonLPrice:

SET ANSI_NULLS OFF
DECLARE @val CHAR(4)
SET @val = NULL
If @val = NULL print 'True'

That is the same thing as testing for null, and to my way of thinking, is not the same is:

DECLARE @val CHAR(4)
DECLARE @Val2 CHAR(4)
SET @val = NULL
SET @Val2 = NULL
If @val = @Val2 print 'True' 
ELSE
Print 'False'

THAT is the type of equivelency testing I was speaking of. The above will return FALSE, because there is no equivelency test between two Null Values (other than both values being NULL, of course).


TysonLPrice:

There is a bit switch indicating there is no value.

Had never really thought about that, but it makes sense. However, it pretty much is the programmatic equivelent of what I said; Null Means "I don't know". The bit switch is SQL Servers way of saying "I don't know".

[TysonLPrice]

TysonLPrice:


That is the same thing as testing for null, and to my way of thinking, is not the same is:

DECLARE @val CHAR(4)
DECLARE @Val2 CHAR(4)
SET @val = NULL
SET @Val2 = NULL
If @val = @Val2 print 'True' 
ELSE
Print 'False'

THAT is the type of equivelency testing I was speaking of. The above will return FALSE, because there is no equivelency test between two Null Values (other than both values being NULL, of course).


TysonLPrice:


Had never really thought about that, but it makes sense. However, it pretty much is the programmatic equivelent of what I said; Null Means "I don't know". The bit switch is SQL Servers way of saying "I don't know".

Put this at the top of your code and it will come back true:
SET ANSI_NULLS OFF

SET ANSI_NULLS OFF
dECLARE @val CHAR(4)
DECLARE @Val2 CHAR(4)
SET @val = NULL
SET @Val2 = NULL
If @val = @Val2 print 'True' 
ELSE
Print 'False'

It's part of how your database is configured. From Microsost:

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If not specified, the setting of the ANSI nulls option of the current database applies. For more information about the ANSI nulls database option, see sp_dboption and Setting Database Options.

I'm not trying to be pedantic. It's just I read an article once that mentioned most programmers will answer the question "what is a null value" with "an empty column", "a column with no Value", "can't be determined", etc. It went on to say it's a bit switch indicating no value (at least Microsoft SQL). So I throw that out there when I can.

Basically I agree with your posts from a programming standpoint.

[RunsWithScissors]

I'm not trying to be pedantic. It's just I read an article once that mentioned most programmers will answer the question "what is a null value" with "an empty column", "a column with no Value", "can't be determined", etc. It went on to say it's a bit switch indicating no value (at least Microsoft SQL). So I throw that out there when I can.

Often, enlightenment and enhanced understanding for both parties are the result of SEMANTIC debate . . .

:-)


THIS Statement:

It's just I read an article once that mentioned most programmers will answer the question "what is a null value" with "an empty column", "a column with no Value", "can't be determined", etc. It went on to say it's a bit switch indicating no value

If taken in the context that the author of the article is trying to say that the CONCEPT of NULL held by most programmers is incorrect, is very nearly oxymoronic. I suspect that the author MAY have been trying to explain how SQL Server itself REPRESENTS null values, and (hopefully) not trying to imly that the idea held by most programmers regarding NULL is incorrect.

Most programming construct simly attempt to MODEL real-world concepts. I mean, when you get right down to it, ALL of the values we see displayed on sceen are nothing more that representations of binary numbers, right? So to say that NULL is really just a bit switch, and does not conceptually mean "unknown value" is the same thing as saying that:

 the integer 85 in your Database isn't REALLY 85, it is REALLY 1 0 1 0 1 0 1

NOTE: This example was "stolen" from a fascinating discussion of Binary numbers and Netwrok packets HERE:

Of course, this example uses a concrete equivelency mapping, whereas NULL is an abstract concept, so I guess it's not really the best example. But if you apply the same logic to the digital representation of an image (say, a bitmap), there might be a better metaphor. A bitmap isn't REALLY a picture; it is a long string of binary values.

re:

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If not specified, the setting of the ANSI nulls option of the current database applies. For more information about the ANSI nulls database option, see sp_dboption and Setting Database Options.

I can see the case for making this an option, as MS obviously did. But I can ALSO see why the folks who devleoped the ANSI standard concluded why their standard would be that NULL values would not compare as equivelent.

In fact, I can see cases in which it would be handy to be able to set NULL columns as equivelent (for example, weeding out zero-length strings vs nulls in text fields), or setting "unknown" values to the side.

Butg it is NOT a logical comparison for most real world concepts.

Are last years earnings "equal to" the earnings of the current year's earnings if we don't know either value yet?

No. (OK, they MIGHT be, but we can't make that statement unless we know). All we can know about them is that we don't know either one, which is why it makes sense to me that we can return both values only of we use a statement like "WHERE AnnualEarnings IS NULL".

Last argument: While SQL Server (apparently) represents NULL values behind the scenes using a bit value (thanks for that, btw . . .) and with ANSI NULLS OFF allows equivelency comparisons (of a sort), in most imperative programming languages, the concept of NULL means Null or Nothing, and there is no way to compare objects with NULL values.

ALL of this is helping ME firm up MY understanding of Null however!

THanks for the info!