Re: 1-year sum question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks once again for the your help Crystal, I am having a little trouble
getting this expression to work in a query. I keep getting the error message
in each line. I will post my expression below, can anyone tell me what I am
doing wrong?

OneYrOut: dsum("DateValue([amount])","[runsumtable]","[date]>= #" & date
&"#<="# and ""[date] < #" & dateserial(year(date)+1,month(date),day(date) &
"#")

I am getting an error message before the query runs. I'm not sure what is
going wrong.
Thanks agian

"strive4peace" <"strive4peace2006 at yaho" wrote:

Hi Nate,

you could use a calculation in the column:

OneYrOut: dsum("DateValue([fieldname])",
"[tablename_or_queryname]",
"[Dafefieldname] >=#"
& date &"# <= "# and "
"[Dafefieldname] < # "
& dateserial(year(date)+1,
month(date), day)date) & "#")


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Nate wrote:
Thank you for the quick response, I am still having trouble with this query
however. Let me try to explain a little better. I would like to add a column
to this query that is the total of the amount column from day 1 to day 365
(the same amount as in the running sum column for day 365). Each day going
forward would be the 1-yr running sum (day 365) total plus the amount from
that day + 365. For instance:

Day________Amount_________RunSum________1-YR Out
1 ________100 _________100 ________12,000 (total amount day
1-365)
2 ________200 _________300 ________12,000 (day 366 has no
amount)
3 ________150 _________450 ________12,100 (day 367's amount=
100)

My running sum column in the query is built with an SQL inner join query. I
realize that I don't need it for this query, I use it for another report.
Would I need to write another SQL statment for this to occur? Would a DLookup
function work to grab the value of runsum day 365? Any help appreciated.

Thank you


"strive4peace" <"strive4peace2006 at yaho" wrote:


make another column in the query:

field --> DateFieldname
show --> No
criteria --> >= DateSerial(
year(date)-1,
month(date),
day(date))

this will get records in the table from the past year

..... but, if I understand you right, you want records from
this date forward, so...

field --> JustDate: DateValue(DateFieldname)
show --> No
criteria --> >= Date()


a little confusing to me is your use of RunningSum, which is
a report property (in A2K)... is this query the recordset of
a report?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Nate wrote:

I have a query as follows:

Date Amount RunningSum
6/01/06 100 100
6/02/06 150 250
etc.

I would like to add another column to this query to calculate the sum of one
year out (i.e. 6/01/06-6/01/07....6/02/06-6/02/07....) I have been having
alot of trouble getting this to work. I would use the sum of the present day
(6/01/06 in this case) as the base date, and then build on it (i.e. this sum
plus any the sum of any activity on 6/02/07). I would appreciate any help
that anyone could give me on this. Has anyone ever built a query similar to
this?

Thanks


.

Relevant Pages

  • RE: Calculated fields in main form based on criteria in sub form
    ... Main form (goat survey table and Total queries) ... NE Sum Query (uses information from the subform, ... other queries differ in what Quadrant equals) ...
    (microsoft.public.access.forms)
  • RE: Calculated fields in main form based on criteria in sub form
    ... Main form (goat survey table and Total queries) ... NE Sum Query (uses information from the subform, ... other queries differ in what Quadrant equals) ...
    (microsoft.public.access.forms)
  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Date Range Totals for Logical Fields
    ... Query reformatted for ease of reading ... Lead Date Lead Date By Month Signed Up Sum Of Active Recruit Sum Of Info ... Recruits Kits Sent Recruits Contact Invites ... Then put your criteria there. ...
    (microsoft.public.access.queries)
  • Re: identify semi-duplicate records, and amend elements of those r
    ... Your idea was my first thought...so I relaxed that rule by allowing the Sum ... called OR how its used specifically within the query may have an impact? ... It could be that the Sum(HH CCY CFAMT) is not returning exactly ZERO. ... the select statement, and filled in specific criterion by criterion, to see ...
    (microsoft.public.access.queries)