Dsum

  • Thread starter Thread starter giorgio
  • Start date Start date
G

giorgio

I desire calculate a running sum in a test box of a form with start event,
and i can use ID or Date field. I used this formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #16/11/2003#") but it
don't work exactly.
thanks
 
Within a SQL clause, you must format the date in American, i.e.:
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #11/16/2003#")

More information on the 3 cases when Access is likely to misunderstand your
dd/mm/yyyy date format:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
2 things that you may wish to look at.
1- It is an extremely bad idea to name columns using
reserved words like Date. Try remaing the column.
2- If my memory is correct, any date literals have to be
entered in the American format of mm/dd/yyyy.

Hope That Helps
Gerald Stanley MCSD
 
Allen Browne said:
Within a SQL clause, you must format the date in American, i.e.:
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #11/16/2003#")

More information on the 3 cases when Access is likely to misunderstand
your
dd/mm/yyyy date format:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

giorgio said:
I desire calculate a running sum in a test box of a form with start
event,
and i can use ID or Date field. I used this formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #16/11/2003#") but it
don't work exactly.
thanks
Click to expand...
Click to expand...

This of the date is point one, but if I write date 16/11/2003 gives back in
every record the total instead and not the running sum. If use the formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[ID] <= Forms![FormsName]![ID]")
makes running the sum (every record the sum with the previous one) but I do
not succeed to fix the beginning moment.
I have tried "[ID] >= idnumber" but the same one does not work. How I can
make?
Regards
 
The given name Date is only an example in order to explain the type of
field. To me it interests a function that it gives back running a sum in
which I can establish the beginning moment.
Gerald Stanley said:
2 things that you may wish to look at.
1- It is an extremely bad idea to name columns using
reserved words like Date. Try remaing the column.
2- If my memory is correct, any date literals have to be
entered in the American format of mm/dd/yyyy.

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----
I desire calculate a running sum in a test box of a form with start event,
and i can use ID or Date field. I used this formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #16/11/2003#") but it
don't work exactly.
thanks


.
Click to expand...
Click to expand...
 
If you are trying to show a running sum and can guarantee the sort is by ID
and that there are no other filters applied:

=Dsum ("[Field of sum]" ; "TableName" ; "[ID] <= " & Nz([ID],0))

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

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

giorgio said:
Allen Browne said:
Within a SQL clause, you must format the date in American, i.e.:
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #11/16/2003#")

More information on the 3 cases when Access is likely to misunderstand
your
dd/mm/yyyy date format:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

giorgio said:
I desire calculate a running sum in a test box of a form with start
event,
and i can use ID or Date field. I used this formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[Date] >= #16/11/2003#") but it
don't work exactly.
thanks
Click to expand...
Click to expand...

This of the date is point one, but if I write date 16/11/2003 gives back in
every record the total instead and not the running sum. If use the formula :
=Dsum ("[Field of sum]" ; "TableName" ; "[ID] <= Forms![FormsName]![ID]")
makes running the sum (every record the sum with the previous one) but I do
not succeed to fix the beginning moment.
I have tried "[ID] >= idnumber" but the same one does not work. How I can
make?
Regards
Click to expand...
 
Back
Top