handle Null

  • Thread starter Thread starter Wei
  • Start date Start date
W

Wei

I am running a crosstab query, which groups some fields,
and sums a field called [loss]. Some entries in the [Loss]
field is empty. So when I run this crosstab, it returns me
a null. Is there anyway to get rid of the null, and turn
it into a zero?
I've tried the following two methods, but neither works...
Method 1
Nz([Your Field], [Value If Your Field Is Null])
Method 2
IIf(IsNull([Your Field]), [Value If Your Field Is Null],
[Your Field])

Thanks in advance for any help.

Wei
 
I'm not a pro with access, but couldn't you set the default value property
to "0"?
 
How about
NZ(Sum(Loss),0)

Or if that doesn't work, how about posting the SQL statement of your query?
 
Thanks a lot John! That worked!

Now may I also ask what the "Val" function does? I can't
find any explanation in "help".

Thanks again.

Wei
-----Original Message-----
How about
NZ(Sum(Loss),0)

Or if that doesn't work, how about posting the SQL statement of your query?


I am running a crosstab query, which groups some fields,
and sums a field called [loss]. Some entries in the [Loss]
field is empty. So when I run this crosstab, it returns me
a null. Is there anyway to get rid of the null, and turn
it into a zero?
I've tried the following two methods, but neither works...
Method 1
Nz([Your Field], [Value If Your Field Is Null])
Method 2
IIf(IsNull([Your Field]), [Value If Your Field Is Null],
[Your Field])

Thanks in advance for any help.

Wei
Click to expand...
.
Click to expand...
 
Thanks Michael! but where is this default value property?

John Spencer gave me something that worked, you can look
at his post...thanks for replying..

Wei

-----Original Message-----
I'm not a pro with access, but couldn't you set the default value property
to "0"?

I am running a crosstab query, which groups some fields,
and sums a field called [loss]. Some entries in the [Loss]
field is empty. So when I run this crosstab, it returns me
a null. Is there anyway to get rid of the null, and turn
it into a zero?
I've tried the following two methods, but neither works...
Method 1
Nz([Your Field], [Value If Your Field Is Null])
Method 2
IIf(IsNull([Your Field]), [Value If Your Field Is Null],
[Your Field])

Thanks in advance for any help.

Wei
Click to expand...


.
Click to expand...
 
Val converts a string into a number.
Val("1234 West Street") --> 1234
Val("001.528") --> 1.528
Val("") --> 0

From the online help for the Val function
MyValue = Val("2457") ' Returns 2457.
MyValue = Val(" 2 45 7") ' Returns 2457.
MyValue = Val("24 and 57") ' Returns 24.

It cannot handle nulls and returns a number until it hits a character that is
not a numeric character, a period, or a space. It will converts hex into
decimal, if I recall correctly.
Thanks a lot John! That worked!

Now may I also ask what the "Val" function does? I can't
find any explanation in "help".

Thanks again.

Wei
-----Original Message-----
How about
NZ(Sum(Loss),0)

Or if that doesn't work, how about posting the SQL statement of your query?


I am running a crosstab query, which groups some fields,
and sums a field called [loss]. Some entries in the [Loss]
field is empty. So when I run this crosstab, it returns me
a null. Is there anyway to get rid of the null, and turn
it into a zero?
I've tried the following two methods, but neither works...
Method 1
Nz([Your Field], [Value If Your Field Is Null])
Method 2
IIf(IsNull([Your Field]), [Value If Your Field Is Null],
[Your Field])

Thanks in advance for any help.

Wei
Click to expand...
.
Click to expand...
Click to expand...
 
I see...thanks!

Wei
-----Original Message-----
Val converts a string into a number.
Val("1234 West Street") --> 1234
Val("001.528") --> 1.528
Val("") --> 0

From the online help for the Val function
MyValue = Val("2457") ' Returns 2457.
MyValue = Val(" 2 45 7") ' Returns 2457.
MyValue = Val("24 and 57") ' Returns 24.

It cannot handle nulls and returns a number until it hits a character that is
not a numeric character, a period, or a space. It will converts hex into
decimal, if I recall correctly.
Thanks a lot John! That worked!

Now may I also ask what the "Val" function does? I can't
find any explanation in "help".

Thanks again.

Wei
-----Original Message-----
How about
NZ(Sum(Loss),0)

Or if that doesn't work, how about posting the SQL statement of your query?



Wei wrote:

I am running a crosstab query, which groups some fields,
and sums a field called [loss]. Some entries in the [Loss]
field is empty. So when I run this crosstab, it
Click to expand...
returns
me
a null. Is there anyway to get rid of the null, and turn
it into a zero?
I've tried the following two methods, but neither works...
Method 1
Nz([Your Field], [Value If Your Field Is Null])
Method 2
IIf(IsNull([Your Field]), [Value If Your Field Is Null],
[Your Field])

Thanks in advance for any help.

Wei
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top