adding null values

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

 

[Allen Browne]

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

 

[Guest]

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.

 

[Allen Browne]

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.

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.

 

[Guest]

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"

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.

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.

 

[Guest]

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

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"

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.

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.

 

[hermie]

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

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"

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.

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.

 

[Allen Browne]

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.

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"

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

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.

 

[Guest]

Use a function for a field which is null
isnull(field,'')
This will replace null values with blank