By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

select sum question

P: 12
Hi, I'm a newbie in Access and SQL. I am trying to write a query in Access that returns the sum of values on a column. The query works fine, the only problem is that if there are no values it return empty. I would like to return value zero in this situation.

My dummy DummyTable table has two columns:
1. Price
2. Date

My query looks like:
Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(DummyTable.Price)  AS TotalSum,
  2. FROM DummyTable
  3. WHERE DummyTable.Date>=StartingDate 
  4. AND DummyTable.Date<=EndingDate;
  5.  
StartingDate and EndingDate are parameters required when to run this query.


Thank you very much for any suggestions,
Cristian
Aug 11 '07 #1
Share this Question
Share on Google+
25 Replies


Scott Price
Expert 100+
P: 1,384
Hi Cristian,

(Deleted original post to correct)

Make sure the default value of your Price field is set to 0.

You will need to rename your field Date to something else, as Date is a reserved word in Access.

Use the Dsum() function instead of Sum(). The Dsum() function has two required arguments of expr and domain. Look it up in M$ helpfile for the specifics.

I used this in my test db, and it returned the 0 figure desired.

If this doesn't work for you please post the sql code you are using.

Regards,
Scott
Aug 11 '07 #2

Scott Price
Expert 100+
P: 1,384
Here is also an excellent basic sql syntax tutorial:

Basic SQL syntax

Regards,
Scott
Aug 11 '07 #3

P: 12
Hi Scott,

thank you very much for the reply. I've tried the following SQL querry in the SQL view:
Expand|Select|Wrap|Line Numbers
  1. DSum("Price", "DummyTable", "MyDate>=StartingDate AND MyDate<=EndingDate");
  2.  
but got an error message:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'".

For the original query:
Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(DummyTable.Price)  AS TotalSum,
  2. FROM DummyTable
  3. WHERE DummyTable.MyDate>=StartingDate
  4. AND DummyTable.MyDate<=EndingDate;
  5.  
I have data for the date between 1/1/2001 and 1/1/2007 for example; but not for 1/1/2100 and 1/1/2200; even thou it's some futuristic date I would still like to display zero value as the result of the query if there's no data. The "Price" field has the 0 value as default defined in the DummyTable.

Cheers,
Cris
Aug 11 '07 #4

Scott Price
Expert 100+
P: 1,384
Your syntax is part of the problem...
Expand|Select|Wrap|Line Numbers
  1. SELECT DSum([MyPrice],"tblDummy") AS TotalPrice, tblDummy.MyDate, tblDummy.DummyID
  2. FROM tblDummy
  3. WHERE (((tblDummy.MyDate)>Date()));
Is the syntax I used in my test db.

However, for cases where there are NO matches whatsoever, the query will still return a null value. Let me research the way around that, and will be back to you promptly :-)

Regards,
Scott
Aug 11 '07 #5

Scott Price
Expert 100+
P: 1,384
Ok, as far as I can tell, there is no way to coerce a query that returns null to 'show' a value of 0 instead without inserting a record that corresponds to your WHERE criteria. If that isn't an option, one workaround involves placing an invisible text box exactly placed over the visible control on your form or report that you are using to interact/display the query results. Then place in the OnOpen event of the form/report, the code that checks for a null value:

i.e. :
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.TotalPrice) Then
Then make the TotalPrice control invisible, and the txtTotalPrice visible by using their respective .visible properties. In VB code this looks like this:

Expand|Select|Wrap|Line Numbers
  1. Me.TotalPrice.Visible = False ' Makes this control invisible
  2. Me.txtTotalPrice.Visible = True ' Makes this control visible
Next set the value of the txtTotalPrice to "0$" or whatever you want it to be, by using this line of code:

Expand|Select|Wrap|Line Numbers
  1. Me.txtTotalPrice = "0$"
End you if statement (syntax is: End If), compile your code (go to Debug menu in VBEditor window, and click first option), save your changes and test your form open...

Let me know if this workaround is something along the lines of what you're looking for.

Regards,
Scott
Aug 11 '07 #6

P: 12
Hi Scott,

thank you again for the prompt reply. What happens if I have something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum1, Sum2, Sum3, (Sum1+Sum2+Sum3) AS SumTotal
  2. FROM
  3.    (SELECT SUM(DummyTable1.Price) AS Sum1,
  4.        FROM DummyTable1
  5.            WHERE DummyTable1.MyDate>=StartingDate
  6.            AND DummyTable1.MyDate<=EndingDate
  7.    ),
  8.  
  9.    (SELECT SUM(DummyTable2.Price) AS Sum2,
  10.        FROM DummyTable2
  11.            WHERE DummyTable2.MyDate>=StartingDate
  12.            AND DummyTable2.MyDate<=EndingDate
  13.    ),
  14.  
  15.    (SELECT SUM(DummyTable3.Price) AS Sum3,
  16.        FROM DummyTable3
  17.            WHERE DummyTable3.MyDate>=StartingDate
  18.            AND DummyTable3.MyDate<=EndingDate
  19.    );
  20.  
Lets say for DummyTable1 and 2 we have values in that Date interval, but not for DummyTable3. SumTotal will be displayed as Null value... Is there no way to display:


Sum1____Sum2____Sum3____SumTotal
-------------------------------------------------------------
value1____value2_____0______value1+value2+0

?

Regards,
Cris
Aug 11 '07 #7

Scott Price
Expert 100+
P: 1,384
Could you repost the sql code of your query, enclosing it first in the code tags (on the top of your reply window look for the # sign. Highlight your sql text and then click the button. Then add =sql, in the first [code] tag, ending up like this [code=sql]?) This makes it easier to read and understand.

Also, is this one query, or three?

Regards,
Scott
Aug 11 '07 #8

Scott Price
Expert 100+
P: 1,384
You can use the iif function to return a 0 value if the value of the query is null in the following manner: Place this statement in the criteria of your MyPrice field.

Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([tblDummy].[MyPrice]),0,[tblDummy].[MyPrice])
(IIF means If and Only If. It has three required arguments: expression, truevalue and falsevalue. It evaluates the expression as true or false, then returns whichever value you provide...)

The problem is that if you are trying to perform a Sum or DSum calculation on the same field at the same time as you use the IIF statement as criteria, you get an error message.

To work around this, you can create three separate queries, then combine them in a fourth query.

I.e. query1 =
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDummy.MyPrice, tblDummy.MyDate, tblDummy.DummyID
  2. FROM tblDummy
  3. WHERE (((tblDummy.MyPrice)=IIf(IsNull([tblDummy].[MyPrice]),0,[tblDummy].[MyPrice])) AND ((tblDummy.MyDate)<Date()));
query2 is the same for dummy table 2, etc for dummy table 3.

In query 4 you place your Sum or DSum statements.

Let me know if this isn't clearer than mud :-)

Regards,
Scott
Aug 11 '07 #9

P: 12
First I'll repost the query:


Expand|Select|Wrap|Line Numbers
  1. SELECT Sum1, Sum2, Sum3, (Sum1+Sum2+Sum3) AS SumTotal
  2. FROM
  3. (SELECT SUM(DummyTable1.Price) AS Sum1,
  4. FROM DummyTable1
  5. WHERE DummyTable1.MyDate>=StartingDate
  6. AND DummyTable1.MyDate<=EndingDate
  7. ),
  8.  
  9. (SELECT SUM(DummyTable2.Price) AS Sum2
  10. FROM DummyTable2
  11. WHERE DummyTable2.MyDate>=StartingDate
  12. AND DummyTable2.MyDate<=EndingDate
  13. ),
  14.  
  15. (SELECT SUM(DummyTable3.Price) AS Sum3
  16. FROM DummyTable3
  17. WHERE DummyTable3.MyDate>=StartingDate
  18. AND DummyTable3.MyDate<=EndingDate
  19. );
  20.  
Aug 11 '07 #10

P: 12
Hi Scott,

I was trying the above query. For the interior query, it returns 0 as expected, but when I try to wrap it in a new query to sum it, it still displays Null value.

I can't figure it out where is the issue because if I try to sum a list of zeros...it should return 0 right?

Regards,
Cris

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(IntermediateSums1)  AS TotalSum1
  2. FROM
  3. (
  4.     SELECT DummyTable1.Price AS IntermediateSums1
  5.         FROM DummyTable1
  6.         WHERE 
  7.         (
  8.             (
  9.                 (DummyTable1.Price)=
  10.                 IIf(
  11.                     IsNull(DummyTable1.Price),
  12.                     0,    
  13.                     DummyTable1.Price
  14.                 )
  15.             )
  16.             AND
  17.             (DummyTable1.MyDate>=StartingDate) 
  18.             AND
  19.              (DummyTable1.MyDate<=EndingDate)
  20.         )
  21. )
  22. ;
  23.  
  24.  
  25.  
  26.  
Aug 11 '07 #11

Scott Price
Expert 100+
P: 1,384
Is this a homework question or are you working on an actual database?

If an actual database, please give me some more details about what data you are actually working with and what you are now trying to accomplish, including the answers to the following questions: Where are you hoping to display this data? How are you wanting to interact with it?

For your question in your 3rd post, what you are trying to accomplish there is much easier done (and more appropriately and efficiently) on a report.

Queries consume much more computer resources, thus resulting in performance issues. Since the IIF statement DOES allow you to display the desired 0$ figure, on a report you can then add a calculated control that totals each of the other sums.

Regards,
Scott
Aug 11 '07 #12

P: 12
Hi Scott,

I am working on an app that is linked to an Access db. I have three tables:

1. TableExpenses1 with a field DateExpenses1, CostExpenses1, ...
2. TableExpenses2 with a field DateExpenses2, CostExpenses2, ...
3. TableExpenses3 with a field DateExpenses3, CostExpenses3, ...

I need to generate a report in Access like this:

Between Starting Date X and Ending Date Y there are the following Expenses:
for Expenses 1 we have E1 dollars
for Expenses 2 we have E2 dollars
for Expenses 3 we have E3 dollars

Total: E1+E2+E3 dollars

That's why I was looking to generate the query from the previous messages.

I've also tried the following query, but still doesn't display any zero value as result when there is no data in that date interval:

Expand|Select|Wrap|Line Numbers
  1. SELECT TotalSumForExpenses1
  2. FROM 
  3. (
  4.     SELECT Sum(IntermediateSums)  AS TotalSumForExpenses1
  5.     FROM
  6.     (
  7.         SELECT DummyTable1.Price AS IntermediateSums
  8.             FROM DummyTable1
  9.             WHERE 
  10.             (
  11.                 (
  12.                     (DummyTable1.Price)=
  13.                     IIf(
  14.                         IsNull(DummyTable1.Price),
  15.                         0,    
  16.                         DummyTable1.Price
  17.                     )
  18.                 )
  19.                 AND
  20.                 (DummyTable1.MyDate>=StartingDate) 
  21.                 AND
  22.                 (DummyTable1.MyDate<=EndingDate)
  23.             )
  24.     )
  25. )
  26. WHERE
  27. (TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
  28. ;
  29.  

Regards,
Cristian
Aug 11 '07 #13

Scott Price
Expert 100+
P: 1,384
Hi Scott,

I am working on an app that is linked to an Access db. I have three tables:

1. TableExpenses1 with a field DateExpenses1, CostExpenses1, ...
2. TableExpenses2 with a field DateExpenses2, CostExpenses2, ...
3. TableExpenses3 with a field DateExpenses3, CostExpenses3, ...

I need to generate a report in Access like this:

Between Starting Date X and Ending Date Y there are the following Expenses:
for Expenses 1 we have E1 dollars
for Expenses 2 we have E2 dollars
for Expenses 3 we have E3 dollars

Total: E1+E2+E3 dollars

That's why I was looking to generate the query from the previous messages.

I've also tried the following query, but still doesn't display any zero value as result when there is no data in that date interval:

Expand|Select|Wrap|Line Numbers
  1. SELECT TotalSumForExpenses1
  2. FROM 
  3. (
  4.     SELECT Sum(IntermediateSums)  AS TotalSumForExpenses1
  5.     FROM
  6.     (
  7.         SELECT DummyTable1.Price AS IntermediateSums
  8.             FROM DummyTable1
  9.             WHERE 
  10.             (
  11.                 (
  12.                     (DummyTable1.Price)=
  13.                     IIf(
  14.                         IsNull(DummyTable1.Price),
  15.                         0,    
  16.                         DummyTable1.Price
  17.                     )
  18.                 )
  19.                 AND
  20.                 (DummyTable1.MyDate>=StartingDate) 
  21.                 AND
  22.                 (DummyTable1.MyDate<=EndingDate)
  23.             )
  24.     )
  25. )
  26. WHERE
  27. (TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
  28. ;
  29.  

Regards,
Cristian

Hi Cristian,

Apologies for the homework question... I was getting worried with all the talk about dummy tables :S.

I'm drawing a blank on how to use sql in how to do what you are asking about... Using the setup we have already discussed (i.e. the IIF statement returning the 0$ figure) I can in my test db display the 0$ in a form, but not in a report.

The way I would work around it (if it were me) is to code this into vba. If you are interested in that method, I can work up a quick and dirty code method that will accomplish what you're looking for. Let me know...

I'll keep poking around meantime, and see if there's something I'm overlooking.

Regards,
Scott
Aug 11 '07 #14

P: 12
Hi Scott,

that's ok. I'm very happy I can discuss with someone this issue. Unfortunately I've spent already 2 days struggling and destroying my brains using with only sql statements to solve the problem. Unfortunately, again, I didn't find any liable solution on the internet, and that's the first motive I've joined this network.

Unfortunately, again, I'm zero in VB so I would like first to try an sql solution. My query as you saw was composed of three sub queries:
I'm getting the sum from all the three tables and I display the generated sum and the sum of all the sums :)

If you find anything that solves this issue using pure sql please let me know.

Regards and many thanks,

Cristian
Aug 11 '07 #15

P: 12
and my big query is:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT TotalSumForExpenses1, TotalSumForExpenses2, TotalSumForExpenses3, TotalSumForExpenses1+TotalSumForExpenses2+TotalSumForExpenses3 AS TotalSumExpenses, StartingDate, EndingDate
  3.  
  4. FROM
  5. (
  6. SELECT TotalSumForExpenses1
  7. FROM 
  8. (
  9.     SELECT Sum(IntermediateSums1)  AS TotalSumForExpenses1
  10.     FROM
  11.     (
  12.         SELECT TableExpenses1.Price AS IntermediateSums1
  13.             FROM TableExpenses1
  14.             WHERE 
  15.             (
  16.                 (
  17.                     (TableExpenses1.Price)=
  18.                     IIf(
  19.                         IsNull(TableExpenses1.Price),
  20.                         0,    
  21.                         TableExpenses1.Price
  22.                     )
  23.                 )
  24.                 AND
  25.                 (TableExpenses1.MyDate>=StartingDate) 
  26.                 AND
  27.                 (TableExpenses1.MyDate<=EndingDate)
  28.             )
  29.     )
  30. )
  31. WHERE
  32. (TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
  33. ),
  34.  
  35.  
  36. (
  37. SELECT TotalSumForExpenses2
  38. FROM 
  39. (
  40.     SELECT Sum(IntermediateSums2)  AS TotalSumForExpenses2
  41.     FROM
  42.     (
  43.         SELECT TableExpenses2.Price AS IntermediateSums2
  44.             FROM TableExpenses2
  45.             WHERE 
  46.             (
  47.                 (
  48.                     (TableExpenses2.Price)=
  49.                     IIf(
  50.                         IsNull(TableExpenses2.Price),
  51.                         0,    
  52.                         TableExpenses2.Price
  53.                     )
  54.                 )
  55.                 AND
  56.                 (TableExpenses2.MyDate>=StartingDate) 
  57.                 AND
  58.                 (TableExpenses2.MyDate<=EndingDate)
  59.             )
  60.     )
  61. )
  62. WHERE
  63. (TotalSumForExpenses2=IIf(IsNull(TotalSumForExpenses2),0,TotalSumForExpenses2))
  64. ),
  65.  
  66.  
  67. (
  68. SELECT TotalSumForExpenses3
  69. FROM 
  70. (
  71.     SELECT Sum(IntermediateSums3)  AS TotalSumForExpenses3
  72.     FROM
  73.     (
  74.         SELECT TableExpenses3.Price AS IntermediateSums3
  75.             FROM TableExpenses3
  76.             WHERE 
  77.             (
  78.                 (
  79.                     (TableExpenses3.Price)=
  80.                     IIf(
  81.                         IsNull(TableExpenses3.Price),
  82.                         0,    
  83.                         TableExpenses3.Price
  84.                     )
  85.                 )
  86.                 AND
  87.                 (TableExpenses3.MyDate>=StartingDate) 
  88.                 AND
  89.                 (TableExpenses3.MyDate<=EndingDate)
  90.             )
  91.     )
  92. )
  93. WHERE
  94. (TotalSumForExpenses3=IIf(IsNull(TotalSumForExpenses3),0,TotalSumForExpenses3))
  95. )
  96. ;
  97.  
Aug 11 '07 #16

Scott Price
Expert 100+
P: 1,384
Hi Cristian,

Glad you're not getting discouraged :-) This is what I've come up with... It's needing a total of 4 queries to get the job done, but I think it's along the lines of what you want.

qryExpense1:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblExpense1.MyPrice) AS TotalExpenses, tblExpense1.MyDate
  2. FROM tblExpense1
  3. GROUP BY tblExpense1.MyDate
  4. HAVING (((tblExpense1.MyDate) Between #6/7/2000# And #6/7/2010#));
qryExpense2:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblExpense2.MyPrice) AS TotalExpenses, tblExpense2.MyDate
  2. FROM tblExpense2
  3. GROUP BY tblExpense2.MyDate
  4. HAVING (((tblExpense2.MyDate) Between #6/7/2000# And #6/7/2010#));
qryExpense3:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblExpense1.MyPrice) AS TotalExpenses, tblExpense1.MyDate
  2. FROM tblExpense1
  3. GROUP BY tblExpense1.MyDate
  4. HAVING (((tblExpense1.MyDate) Between #6/7/2000# And #6/7/2010#));
rqryExpenses:
Expand|Select|Wrap|Line Numbers
  1. SELECT TotalExpenses, MyDate From qryExpense1
  2. Union
  3. Select TotalExpenses, MyDate From qryExpense2
  4. UNION Select TotalExpenses, MyDate From qryExpense3;
Now in design view of your report called something like rptExpenses, place a text box in the Report Footer section called Grand & Glorious Total Expenses (or whatever you want it to be...) in this text box write the following: =Sum([TotalExpenses])

This SHOULD be along the lines of what you want. Hope so, anyway :-)

Kind regards,
Scott
Aug 11 '07 #17

P: 12
Hi Scott,

I got in a MS ACCESS book a solution like:
Nz([YourFieldName or expression],0)

SELECT Nz(Sum(IntermediateSums3),0) AS TotalSumForExpenses3

which returns 0 if Sum(IntermediateSum3) is NULL.

Thank you very much for not letting me loosing my brains,

Cris
Aug 12 '07 #18

Scott Price
Expert 100+
P: 1,384
Hi Scott,

I got in a MS ACCESS book a solution like:
Nz([YourFieldName or expression],0)

SELECT Nz(Sum(IntermediateSums3),0) AS TotalSumForExpenses3

which returns 0 if Sum(IntermediateSum3) is NULL.

Thank you very much for not letting me loosing my brains,

Cris
Glad you got something workable figured out! I was aware of the Nz() function, and tested it first thing, but must have made my syntax wrong because it didn't work ;-(

Anyway, good luck on your app.

Regards,
Scott
Aug 12 '07 #19

P: 12
Hi Scott,

sorry to come back. There's now another problem. After I'm exporting the report in HTML format and run the file, got the following error:

"Data provider failed while executing a provider command"
and
"Undefined function 'NZ' in expression".

How can I make it working also in HTML format?

I'm reposting the query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT TotalSumForExpenses1, TotalSumForExpenses2, TotalSumForExpenses3, VAL(TotalSumForExpenses1)+VAL(TotalSumForExpenses2)+VAL(TotalSumForExpenses3) AS TotalSumExpenses, StartingDate, EndingDate
  3.  
  4. FROM
  5. (
  6. SELECT TotalSumForExpenses1
  7. FROM 
  8. (
  9.     SELECT NZ(Sum(IntermediateSums1),0)  AS TotalSumForExpenses1
  10.     FROM
  11.     (
  12.         SELECT TableExpenses1.Price AS IntermediateSums1
  13.             FROM TableExpenses1
  14.             WHERE 
  15.             (
  16.                 (
  17.                     (TableExpenses1.Price)=
  18.                     IIf(
  19.                         IsNull(TableExpenses1.Price),
  20.                         0,    
  21.                         TableExpenses1.Price
  22.                     )
  23.                 )
  24.                 AND
  25.                 (TableExpenses1.MyDate>=StartingDate) 
  26.                 AND
  27.                 (TableExpenses1.MyDate<=EndingDate)
  28.             )
  29.     )
  30. )
  31. ),
  32.  
  33.  
  34. (
  35. SELECT TotalSumForExpenses2
  36. FROM 
  37. (
  38.     SELECT NZ(Sum(IntermediateSums2),0)  AS TotalSumForExpenses2
  39.     FROM
  40.     (
  41.         SELECT TableExpenses2.Price AS IntermediateSums2
  42.             FROM TableExpenses2
  43.             WHERE 
  44.             (
  45.                 (
  46.                     (TableExpenses2.Price)=
  47.                     IIf(
  48.                         IsNull(TableExpenses2.Price),
  49.                         0,    
  50.                         TableExpenses2.Price
  51.                     )
  52.                 )
  53.                 AND
  54.                 (TableExpenses2.MyDate>=StartingDate) 
  55.                 AND
  56.                 (TableExpenses2.MyDate<=EndingDate)
  57.             )
  58.     )
  59. )
  60. ),
  61.  
  62.  
  63. (
  64. SELECT TotalSumForExpenses3
  65. FROM 
  66. (
  67.     SELECT NZ(Sum(IntermediateSums3),0)  AS TotalSumForExpenses3
  68.     FROM
  69.     (
  70.         SELECT TableExpenses3.Price AS IntermediateSums3
  71.             FROM TableExpenses3
  72.             WHERE 
  73.             (
  74.                 (
  75.                     (TableExpenses3.Price)=
  76.                     IIf(
  77.                         IsNull(TableExpenses3.Price),
  78.                         0,    
  79.                         TableExpenses3.Price
  80.                     )
  81.                 )
  82.                 AND
  83.                 (TableExpenses3.MyDate>=StartingDate) 
  84.                 AND
  85.                 (TableExpenses3.MyDate<=EndingDate)
  86.             )
  87.     )
  88. )
  89. )
  90. ;
  91.  
Any ideas?

Thank you,
Cristian
Aug 12 '07 #20

Scott Price
Expert 100+
P: 1,384
Boys, Cristian, Sorry to say I'm a big zero on HTML! Please repost this last section under a new thread with title: "Access Nz() function exporting to HTML?" or something similar to it. Some of our more HTML savvy members can take it from there.

If the Nz() still won't work, you could try the union query idea I suggested in a previous post, to see if it will allow you to accomplish the objective?

Regards,
Scott
Aug 12 '07 #21

Scott Price
Expert 100+
P: 1,384
Another thought here: just reading up on Nz(), it is a Jet specific function, not a VBA function, so I'm not sure if it even will allow upsizing to either TSQL or any other programming language (such as HTML).

Anyway, somebody more smarter than I on this site probably knows the answer "al toque" as we say in Spanish.

Regards,
Scott
Aug 12 '07 #22

P: 12
Hi Scott,

I saw the solution you provided in the previous messages and looks much better than my huge query. I short question: how do you return 0 instead of NULL?

Regards,

Cris
Aug 12 '07 #23

Scott Price
Expert 100+
P: 1,384
Hi Scott,

I saw the solution you provided in the previous messages and looks much better than my huge query. I short question: how do you return 0 instead of NULL?

Regards,

Cris
After playing around with Sum() and DSum() in queries and subqueries and textboxes until I got tired of looking at wrong numbers, I came up with this solution. It's not maybe quite what you want, as it involves the use of VBA code, but it works! (I always like things that work, rather than things that SHOULD work... Smile)

If you're interested in seeing it, let me know and I'll email you the test db I used to construct the thing.

Otherwise, I'm getting short on answers... There are all sorts of hidden pitfalls when you have null values, and the supposedly easy answers haven't turned out to be very easy!

Probably someone else would handle this differently and more easily, but basically I used 6 queries in the end, tied to 6 hidden forms. Then in the report OnOpen event I wrote the code to populate the Expense1Totals, Expense2Totals, Expense3Totals and GrandTotals with the correct values. There's a lot more to it than just that, but that is a brief resumé of what I did.

Regards,
Scott
Aug 12 '07 #24

FishVal
Expert 2.5K+
P: 2,653
Hi, Cristian.

Nz() is VBA function available in Access only.
Some SQL dialects support CASE WHEN..THEN..ELSE and COALESCE operators.

Expand|Select|Wrap|Line Numbers
  1. SELECT CASE WHEN (SUM(DummyTable.Price) IS NULL) THEN 0 ELSE SUM(DummyTable.Price),
  2. FROM DummyTable
  3. WHERE DummyTable.Date>=StartingDate 
  4. AND DummyTable.Date<=EndingDate;
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT COALESCE (SUM(DummyTable.Price), 0)
  2. FROM DummyTable
  3. WHERE DummyTable.Date>=StartingDate 
  4. AND DummyTable.Date<=EndingDate;
  5.  
Aug 12 '07 #25

Scott Price
Expert 100+
P: 1,384
FishVal,

Thanks for your input... A quick comment on your statement here:
Nz() is VBA function available in Access only.
Some SQL dialects support CASE WHEN..THEN..ELSE and COALESCE operators.
Probably a bit nitpicky, but my desk reference says this about Nz:

The Jet expression service supplies the Nz function; it's not a VBA reserved word, so it doesn't appear in bold type.
So technically speaking, it really isn't a VBA function at all. Again, just a bit nitpicky perhaps, but may mean that Nz() will not work in anything outside of Access, even other members of the Office family that use the common VBA programming language (haven't tried it to find out, and don't think I will, either... this comment is just in case someone tries it thinking it SHOULD work, and it won't)?

Regards,
Scott
Aug 12 '07 #26

Post your reply

Sign in to post your reply or Sign up for a free account.