handle Null

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
 
M

Michael Beatty

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

John Spencer (MVP)

How about
NZ(Sum(Loss),0)

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

Wei

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...
 
W

Wei

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...
 
J

John Spencer (MVP)

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...
 
W

Wei

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...
 
Top