adding null values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to add two subquerries in a querry. one of the subquerries sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is zero
even though there is a value for b. Maybe I need to know how to make a
querry return a zero value even thought the qerry has no value to return.
 
That didn't help ,but I can tell you understand what I am trying to do. Also
when I use "nz" it does change to "Nz" like all other fucntions, but no error
messages. Data Types are all the same "currency". I can't believe I am have
so much trouble with this. It seems to be a very basic querry to write.

Allen Browne said:
JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery statements, and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is zero
even though there is a value for b. Maybe I need to know how to make a
querry return a zero value even thought the qerry has no value to return.
Click to expand...
Click to expand...
 
Swith the query to SQL view, and post the entire SQL statement.
I'll try to look at what you posted tomorrow. (1am here).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
That didn't help ,but I can tell you understand what I am trying to do.
Also
when I use "nz" it does change to "Nz" like all other fucntions, but no
error
messages. Data Types are all the same "currency". I can't believe I am
have
so much trouble with this. It seems to be a very basic querry to write.

Allen Browne said:
JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery statements,
and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is
zero
even though there is a value for b. Maybe I need to know how to make
a
querry return a zero value even thought the qerry has no value to
return.
Click to expand...
Click to expand...
Click to expand...
 
SELECT Sum(qryWeeklySum4cs.SumOfSumOfAmount) AS 4,
Sum(qryWeeklySum4csSetters.SumOfSumOfAmount) AS 4S,
CDbl(nz([4],0)-CDbl(nz([4s],0))) AS Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;

"qryWeeklySum4cs" is what $$ is owed to me and "qryWeeklySum4csSetters" is
what $$ I owe. What I owe shows up in the Table (used in the two
subquerries) before what is owed to me. I should be seeing a negitive value
in "Expr1" instead I get "Blank" "Blank" "Zero"

Allen Browne said:
Swith the query to SQL view, and post the entire SQL statement.
I'll try to look at what you posted tomorrow. (1am here).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
That didn't help ,but I can tell you understand what I am trying to do.
Also
when I use "nz" it does change to "Nz" like all other fucntions, but no
error
messages. Data Types are all the same "currency". I can't believe I am
have
so much trouble with this. It seems to be a very basic querry to write.

Allen Browne said:
JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery statements,
and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is
zero
even though there is a value for b. Maybe I need to know how to make
a
querry return a zero value even thought the qerry has no value to
return.
Click to expand...
Click to expand...
Click to expand...
 
Haggr

I would say:
1 - Convert the values indenpendently
2 - Convert them to Currency instead

SELECT Sum(qryWeeklySum4cs.SumOfSumOfAmount) AS 4,
Sum(qryWeeklySum4csSetters.SumOfSumOfAmount) AS 4S,
(CCur(nz([4],0)) - CCur(nz([4s],0))) AS Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;


NOTE: I've changed the parentesis and CDbl to CCur

I hope I could be of help

Take care
Mauricio Silva
haggr said:
SELECT Sum(qryWeeklySum4cs.SumOfSumOfAmount) AS 4,
Sum(qryWeeklySum4csSetters.SumOfSumOfAmount) AS 4S,
CDbl(nz([4],0)-CDbl(nz([4s],0))) AS Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;

"qryWeeklySum4cs" is what $$ is owed to me and "qryWeeklySum4csSetters" is
what $$ I owe. What I owe shows up in the Table (used in the two
subquerries) before what is owed to me. I should be seeing a negitive value
in "Expr1" instead I get "Blank" "Blank" "Zero"

Allen Browne said:
Swith the query to SQL view, and post the entire SQL statement.
I'll try to look at what you posted tomorrow. (1am here).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
That didn't help ,but I can tell you understand what I am trying to do.
Also
when I use "nz" it does change to "Nz" like all other fucntions, but no
error
messages. Data Types are all the same "currency". I can't believe I am
have
so much trouble with this. It seems to be a very basic querry to write.

:

JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery statements,
and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is
zero
even though there is a value for b. Maybe I need to know how to make
a
querry return a zero value even thought the qerry has no value to
return.
Click to expand...
Click to expand...
Click to expand...
 
Think you have 1 bracket too much in CDbl(nz([4],0)-CDbl(nz([4s],0))) AS
Expr1
try CDbl(nz([4],0)-CDbl(nz([4s],0)) AS Expr1
hermie

haggr said:
SELECT Sum(qryWeeklySum4cs.SumOfSumOfAmount) AS 4,
Sum(qryWeeklySum4csSetters.SumOfSumOfAmount) AS 4S,
CDbl(nz([4],0)-CDbl(nz([4s],0))) AS Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;

"qryWeeklySum4cs" is what $$ is owed to me and "qryWeeklySum4csSetters" is
what $$ I owe. What I owe shows up in the Table (used in the two
subquerries) before what is owed to me. I should be seeing a negitive value
in "Expr1" instead I get "Blank" "Blank" "Zero"

Allen Browne said:
Swith the query to SQL view, and post the entire SQL statement.
I'll try to look at what you posted tomorrow. (1am here).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
That didn't help ,but I can tell you understand what I am trying to do.
Also
when I use "nz" it does change to "Nz" like all other fucntions, but no
error
messages. Data Types are all the same "currency". I can't believe I am
have
so much trouble with this. It seems to be a very basic querry to write.

:

JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery statements,
and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is
zero
even though there is a value for b. Maybe I need to know how to make
a
querry return a zero value even thought the qerry has no value to
return.
Click to expand...
Click to expand...
Click to expand...
 
Interesting query. Cartesian product between 2 other queries that look like
they are each stacked at least 2 more levels deep.

Avoid a number as a field name. In some contexts, 4 can be interpreted as
meaning "the 4th field". It may be worth converting the first 2 fields
individually. And it looks like you want currency rather than double.

Try:
SELECT CCur(Nz(Sum(qryWeeklySum4cs.SumOfSumOfAmount),0)) AS Sum4,
CCur(Nz(Sum(qryWeeklySum4csSetters.SumOfSumOfAmount),0)) AS Settlers4s,
Sum4 + Settlers4s As Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

haggr said:
SELECT Sum(qryWeeklySum4cs.SumOfSumOfAmount) AS 4,
Sum(qryWeeklySum4csSetters.SumOfSumOfAmount) AS 4S,
CDbl(nz([4],0)-CDbl(nz([4s],0))) AS Expr1
FROM qryWeeklySum4cs, qryWeeklySum4csSetters;

"qryWeeklySum4cs" is what $$ is owed to me and "qryWeeklySum4csSetters" is
what $$ I owe. What I owe shows up in the Table (used in the two
subquerries) before what is owed to me. I should be seeing a negitive
value
in "Expr1" instead I get "Blank" "Blank" "Zero"

Allen Browne said:
Swith the query to SQL view, and post the entire SQL statement.
I'll try to look at what you posted tomorrow. (1am here).


haggr said:
That didn't help ,but I can tell you understand what I am trying to do.
Also
when I use "nz" it does change to "Nz" like all other fucntions, but no
error
messages. Data Types are all the same "currency". I can't believe I
am
have
so much trouble with this. It seems to be a very basic querry to
write.

:

JET is likely to misunderstand the data types, so try:
CDbl(Nz([a], 0)) + CDbl(Nz(, 0))

That's assuming that [a] and represent complete subquery
statements,
and
both return a numeric field.

For more info on typecasting calculated fields, see::
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html


Trying to add two subquerries in a querry. one of the subquerries
sometimes
returns no value. I still need them to add Tried "expr1: nz([a],0)
+nz(,0)". That returns zero. A is blank b is blank and expr1 is
zero
even though there is a value for b. Maybe I need to know how to
make
a
querry return a zero value even thought the qerry has no value to
return.
Click to expand...
Click to expand...
Click to expand...
 
Use a function for a field which is null
isnull(field,'')
This will replace null values with blank
 
Back
Top