Query Expressions

[Steven Phillips via AccessMonster.com]

In my query I have Expr1:---,Expr2:---, etc. How can I sum these expression
columns into another column within the same query?

 

[Steven Phillips via AccessMonster.com]

It's me,
Sorry I did not mention that I am using DLookUp in the event it impacts
your answer.
Thanks!

 

[Ken Snell [MVP]]

MySummedValue: [Expr1] + [Expr2] + [Expr3]

 

[Steven Phillips via AccessMonster.com]

Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]

MySummedValue: [Expr1] + [Expr2] + [Expr3]

 

[PC Datasheet]

SumOfExprs:[Expr1] + [Expr2] + [Expr3] .........

 

[Steven Phillips via AccessMonster.com]

Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
answer of 121233321, it should have been 675.

In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
SumOfExprs:[Expr1] + [Expr2] + [Expr3]

 

[Ken Snell [MVP]]

Post the entire SQL statement. It may be that you'll need to repeat the
expression from each of the fields and add them.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]

MySummedValue: [Expr1] + [Expr2] + [Expr3]

In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?

 

[Steven Phillips via AccessMonster.com]

Ken,

SELECT DLookUp("[SumOfSumOfPieces]","[DAILY periphery grind ManEwag]") AS
Expr1, DLookUp("[SumOfPieces]","[month prhy grind ManEwag]") AS Expr2,
DLookUp("[SumOfSumOfPieces]","[DAILY periphery grind cncewag]") AS Expr3,
DLookUp("[SumOfPieces]","[month prhy grind cncewag]") AS Expr4, DLookUp("
[SumOfSumOfPieces]","[DAILY Periphery Grind waida's]") AS Expr5, DLookUp("
[SumOfPieces]","[month prhy Grind waida's]") AS Expr6, DLookUp("
[SumOfSumOfPieces]","[Total Daily Grind Kyon Query]") AS Expr7, DLookUp("
[SumOfSumOfPieces]","[Total month Prhy Grind Kyon]") AS Expr8, [Expr1]+
[Expr3]+[Expr5] AS MySummedValue;

 

[Ken Snell [MVP]]

Can Expr1 or Expr2 or Expr3 contain Null values? If yes, then the Null
propogates through the addition and the result will always be Null. For
example:
Expr1 = Null
Expr2 = 15
Expr3 = 10

Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null

Assuming that they might contain Null, and that in those cases the Null
should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue

 

[Ken Snell [MVP]]

By the way, did you notice that you used Expr5 instead of Expr3 in your
expression?

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]

MySummedValue: [Expr1] + [Expr2] + [Expr3]

In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?

 

[Guest]

Check your data types --

the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.

Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
answer of 121233321, it should have been 675.

In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
SumOfExprs:[Expr1] + [Expr2] + [Expr3]

 

[Steven Phillips via AccessMonster.com]

Ken,
I did need the correction you stated for null values. I discovered after
my first response to you that there was a null value in one of the fields
and therefore it resulted as you said. I have updated the query with this
new calculation but my result is incorrect. The response from another
individual (below***) is on target but I don't recognize the function. The
sum I get is as he stated.

Expr1 = Null
Expr2 = 15
Expr3 = 10

Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null

Assuming that they might contain Null, and that in those cases the Null

should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue

Thanks again but I'm not getting the right answer. SumOfExprs: yielded an

answer of 121233321, it should have been 675.

***
the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.

 

[Ken Snell [MVP]]

Change the calculated field to this:

CInt(Nz([Expr1],0))+CInt(Nz([Expr3],0))+CInt(Nz([Expr5],0)) AS MySummedValue


Note that using the CInt function assumes that the numbers will be integers
between -32768 and 32767. If the number will be larger (or smaller) than
this (numbers between -2,147,483,648 to 2,147,483,647), then use CLng
function instead of CInt.

If the numbers can be fractional (e.g., 34.57), then use CDbl function.
--

Ken Snell
<MS ACCESS MVP>

Ken,
I did need the correction you stated for null values. I discovered after
my first response to you that there was a null value in one of the fields
and therefore it resulted as you said. I have updated the query with this
new calculation but my result is incorrect. The response from another
individual (below***) is on target but I don't recognize the function. The
sum I get is as he stated.

Expr1 = Null
Expr2 = 15
Expr3 = 10

Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null

Assuming that they might contain Null, and that in those cases the Null

should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue

Thanks again but I'm not getting the right answer. SumOfExprs: yielded an

answer of 121233321, it should have been 675.

***
the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.

 

[Steven Phillips via AccessMonster.com]

Ken,
That is what it needed, Thanks so much!! Can I possibly ask another
question of you? This query pulls it's data from a table which will
accumulate production data month after month, etc. Two of the expressions
we have been discussing will need to recognize a new month and therefore
not include the prior month in the total. Is it better to do this here, or
when I create the report based on this data.
Expressions 2,4,6 are monthly totals for 3 groups, this calculation will
need to recognize the new month, or the individual expressions
themselves,whichever you suggest.
Expressions 1,3,5 are daily totals and are self maintained with Date()-1


Clng(Nz([Expr2],0))+Clng(Nz([Expr4],0))+Clng(Nz([Expr6],0)) AS
MySummedValue1

Clng(Nz([Expr1],0))+Clng(Nz([Expr3],0))+Clng(Nz([Expr5],0)) AS

MySummedValue

 

[Ken Snell [MVP]]

I really can't give you a good answer, as I have no idea of the table
structure nor the data that are in them. In general, it's best to filter the
data as early in the process as possible, so the initial queries would be
best for such date/month filtering, not in this final query. But, as I
noted, it's really difficult to give any specific suggestions based on what
I know (and don't know) about your data and database.

Also, do note that DLookup function is not very fast in a query, so you may
find that the query will run much slower than if you used a query to get the
data from your tables and queries.

--

Ken Snell
<MS ACCESS MVP>

Ken,
That is what it needed, Thanks so much!! Can I possibly ask another
question of you? This query pulls it's data from a table which will
accumulate production data month after month, etc. Two of the expressions
we have been discussing will need to recognize a new month and therefore
not include the prior month in the total. Is it better to do this here, or
when I create the report based on this data.
Expressions 2,4,6 are monthly totals for 3 groups, this calculation will
need to recognize the new month, or the individual expressions
themselves,whichever you suggest.
Expressions 1,3,5 are daily totals and are self maintained with Date()-1


Clng(Nz([Expr2],0))+Clng(Nz([Expr4],0))+Clng(Nz([Expr6],0)) AS
MySummedValue1

Clng(Nz([Expr1],0))+Clng(Nz([Expr3],0))+Clng(Nz([Expr5],0)) AS

MySummedValue