how to sum

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

Jean-Paul

Hi
I have following query:
SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
GROUP BY Betalingen.DATUM
HAVING (((Betalingen.DATUM) Between
Format([Forms]![betalingen]![begin],"Short Date") And
Format([Forms]![betalingen]![einde],"Short Date")));

This should return the sum of all values "bedrag_b" for the period I defined

However,
I get a sum grouped per date and not for the whole period

What to change

Thanks
 
Change the HAVING clause to a WHERE Clause

SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
WHERE Betalingen.DATUM Between Format([Forms]![betalingen]![begin],"Short
Date") And Format([Forms]![betalingen]![einde],"Short Date")
GROUP BY Betalingen.DATUM

I am concerned that you are using the format function in the query. Is Datum
a string field? If not, you could be getting erroneous results. If so, you
very probably are going to get erroneous results.

Based on your field and table names, I am guessing you are not it the US and
therefore are not using US date formats. I suggest you read about
International Dates in Access at:
http://allenbrowne.com/ser-36.html



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
OK... I changed some things
Now I have:

SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
WHERE Betalingen.DATUM Between Forms!betalingen!begin And
Forms!betalingen!einde;

This seems to work
I stored this query in a file called: BVBA_Totaal

In the form footer I created a field called tot and entered:

=BVBA_Totaal!SomVanBEDRAG_b

I get a #name? error in the field

The query on itself runs perfectly and returns the correct sum

What am I doing wrong?


John said:
Change the HAVING clause to a WHERE Clause

SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
WHERE Betalingen.DATUM Between
Format([Forms]![betalingen]![begin],"Short Date") And
Format([Forms]![betalingen]![einde],"Short Date")
GROUP BY Betalingen.DATUM

I am concerned that you are using the format function in the query. Is
Datum a string field? If not, you could be getting erroneous results.
If so, you very probably are going to get erroneous results.

Based on your field and table names, I am guessing you are not it the US
and therefore are not using US date formats. I suggest you read about
International Dates in Access at:
http://allenbrowne.com/ser-36.html



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jean-Paul said:
Hi
I have following query:
SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
GROUP BY Betalingen.DATUM
HAVING (((Betalingen.DATUM) Between
Format([Forms]![betalingen]![begin],"Short Date") And
Format([Forms]![betalingen]![einde],"Short Date")));

This should return the sum of all values "bedrag_b" for the period I
defined

However,
I get a sum grouped per date and not for the whole period

What to change

Thanks
Click to expand...
Click to expand...
 
Well, unless that query is the source for the form, you cannot reference the
field in that manner. And even if it were the source for the form there is
the problem that it would be the only value available and could be referred to
as [SomVanbedRag_B]

You could use the following.
=DLookup("SomVanbedRag_B","BVBA_Totaal")

This assumes that the form named "betalingen" is open.

You could also use
= DSum("BEDRAG_b","Betalingen","DATUM Between #" &
Format(Forms!betalingen!begin,"yyyy-mm-dd") & "# And #" &
Format(Forms!betalingen!einde,"yyyy-mm-dd") & "#")

But if the form you are doing this all on is betalingen and all you want is
the total of "BedRag_B" and that is in the form's recordset already, then all
you need in the control is
=Sum([Bedrag_b])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
great help... problem solved
Thanks

John said:
Well, unless that query is the source for the form, you cannot reference
the field in that manner. And even if it were the source for the form
there is the problem that it would be the only value available and could
be referred to as [SomVanbedRag_B]

You could use the following.
=DLookup("SomVanbedRag_B","BVBA_Totaal")

This assumes that the form named "betalingen" is open.

You could also use
= DSum("BEDRAG_b","Betalingen","DATUM Between #" &
Format(Forms!betalingen!begin,"yyyy-mm-dd") & "# And #" &
Format(Forms!betalingen!einde,"yyyy-mm-dd") & "#")

But if the form you are doing this all on is betalingen and all you want
is the total of "BedRag_B" and that is in the form's recordset already,
then all you need in the control is
=Sum([Bedrag_b])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jean-Paul said:
OK... I changed some things
Now I have:

SELECT Sum(Betalingen.BEDRAG_b) AS SomVanBEDRAG_b
FROM Betalingen
WHERE Betalingen.DATUM Between Forms!betalingen!begin And
Forms!betalingen!einde;

This seems to work
I stored this query in a file called: BVBA_Totaal

In the form footer I created a field called tot and entered:

=BVBA_Totaal!SomVanBEDRAG_b

I get a #name? error in the field

The query on itself runs perfectly and returns the correct sum

What am I doing wrong?
Click to expand...
Click to expand...
 
Back
Top