Dailly Running Totals

  • Thread starter Thread starter Matt Reed
  • Start date Start date
M

Matt Reed

Hello,
I have been trying to modify the sample as shown in 290136 "How to Create
Running Totals in a Query". Added a column for ADay
DatePart('d',[OrderDate]) and modified the RunTot expression as follows.

RunTot: DSum("Freight","Orders","DatePart('d',[OrderDate])<=" & [ADay] &
"And DatePart'm',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',
[OrderDate])<=" & [AYear] & "")

I get a daily total, but it starts over at zero everymonth. Where I only
want it to start at zero every year.

Any suggestions on what to change?

Thanks for looking and thanks in advance for any help you can give me.

Matt
 
Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP
 
Michel, Thanks for the quick reply.

Your point makes a lot of sense. I liked the way you used feet and inches to
make me see your point. It helped.

When I actually tried to use it I am getting an error.

The expression I entered was:
RunTot: DSum("Freight","Orders","[OrderDate]<=DateSerial( [AYear]
,[AMonth],[ADay])")

The error I got was:
"Microsoft Access can't find the name 'AYear' you used in the
expression.

Question 1 is what did I enter wrong?

More importantly...

Question 2: When I tried to search for DataSerial in access help I get
nothing found or rephrase the question - Is there a more complete help file
available?

Thanks for your help.

Matt

Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,


I don't know what is AYear, I copied it from your message.

It should be DateSerial, not DataSerial. It accepts three argument,
the year, the month, and the day, and return the date that makes that.



Hoping it may help,
Vanderghast, Access MVP



Matt Reed said:
Michel, Thanks for the quick reply.

Your point makes a lot of sense. I liked the way you used feet and inches to
make me see your point. It helped.

When I actually tried to use it I am getting an error.

The expression I entered was:
RunTot: DSum("Freight","Orders","[OrderDate]<=DateSerial( [AYear]
,[AMonth],[ADay])")

The error I got was:
"Microsoft Access can't find the name 'AYear' you used in the
expression.

Question 1 is what did I enter wrong?

More importantly...

Question 2: When I tried to search for DataSerial in access help I get
nothing found or rephrase the question - Is there a more complete help file
available?

Thanks for your help.

Matt

Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Hello,
I have been trying to modify the sample as shown in 290136 "How to Create
Running Totals in a Query". Added a column for ADay
DatePart('d',[OrderDate]) and modified the RunTot expression as follows.

RunTot: DSum("Freight","Orders","DatePart('d',[OrderDate])<=" & [ADay] &
"And DatePart'm',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',
[OrderDate])<=" & [AYear] & "")

I get a daily total, but it starts over at zero everymonth. Where I only
want it to start at zero every year.

Any suggestions on what to change?

Thanks for looking and thanks in advance for any help you can give me.

Matt
Click to expand...
Click to expand...
 
Thanks for the quick reply.
I entered "DateSerial" in Access help - and I get [Please rephrase your
question]
This is WinXP SP-2 full install.
Should I be able to look this function up in Access help ?

Matt
Hi,


I don't know what is AYear, I copied it from your message.

It should be DateSerial, not DataSerial. It accepts three argument,
the year, the month, and the day, and return the date that makes that.



Hoping it may help,
Vanderghast, Access MVP



Matt Reed said:
Michel, Thanks for the quick reply.

Your point makes a lot of sense. I liked the way you used feet and inches to
make me see your point. It helped.

When I actually tried to use it I am getting an error.

The expression I entered was:
RunTot: DSum("Freight","Orders","[OrderDate]<=DateSerial( [AYear]
,[AMonth],[ADay])")

The error I got was:
"Microsoft Access can't find the name 'AYear' you used in the
expression.

Question 1 is what did I enter wrong?

More importantly...

Question 2: When I tried to search for DataSerial in access help I get
nothing found or rephrase the question - Is there a more complete help file
available?

Thanks for your help.

Matt

Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Hello,
I have been trying to modify the sample as shown in 290136 "How to Create
Running Totals in a Query". Added a column for ADay
DatePart('d',[OrderDate]) and modified the RunTot expression as follows.

RunTot: DSum("Freight","Orders","DatePart('d',[OrderDate])<=" & [ADay] &
"And DatePart'm',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',
[OrderDate])<=" & [AYear] & "")

I get a daily total, but it starts over at zero everymonth. Where I only
want it to start at zero every year.

Any suggestions on what to change?

Thanks for looking and thanks in advance for any help you can give me.

Matt
Click to expand...
Click to expand...
 
Hi,

I typed it, DateSerial, in the immediate debug window, and while the
cursor was on it, click F1. The help file find it, that way... and, with
2003, in the IDE, not in the "Access" opening window, the help returned the
same article if I typed DateSerial as keyword I was looking for. Help in
2003 is better than in 2002, on the other hand, that's a fact. I don't have
2002 anymore installed, I can't tell about that version.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Thanks for the quick reply.
I entered "DateSerial" in Access help - and I get [Please rephrase your
question]
This is WinXP SP-2 full install.
Should I be able to look this function up in Access help ?

Matt
Hi,


I don't know what is AYear, I copied it from your message.

It should be DateSerial, not DataSerial. It accepts three argument,
the year, the month, and the day, and return the date that makes that.



Hoping it may help,
Vanderghast, Access MVP



Matt Reed said:
Michel, Thanks for the quick reply.

Your point makes a lot of sense. I liked the way you used feet and
Click to expand...
inches
to
make me see your point. It helped.

When I actually tried to use it I am getting an error.

The expression I entered was:
RunTot: DSum("Freight","Orders","[OrderDate]<=DateSerial( [AYear]
,[AMonth],[ADay])")

The error I got was:
"Microsoft Access can't find the name 'AYear' you used in the
expression.

Question 1 is what did I enter wrong?

More importantly...

Question 2: When I tried to search for DataSerial in access help I get
nothing found or rephrase the question - Is there a more complete help file
available?

Thanks for your help.

Matt

Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Hello,
I have been trying to modify the sample as shown in 290136 "How to Create
Running Totals in a Query". Added a column for ADay
DatePart('d',[OrderDate]) and modified the RunTot expression as follows.

RunTot: DSum("Freight","Orders","DatePart('d',[OrderDate])<=" & [ADay] &
"And DatePart'm',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',
[OrderDate])<=" & [AYear] & "")

I get a daily total, but it starts over at zero everymonth. Where I only
want it to start at zero every year.

Any suggestions on what to change?

Thanks for looking and thanks in advance for any help you can give me.

Matt
Click to expand...
Click to expand...
Click to expand...
 
Thanks..

That got me where I needed to be.

Was looking at help in access and was getting real frustrated with the lack
of information.

Well here I go.

Matt

Hi,

I typed it, DateSerial, in the immediate debug window, and while the
cursor was on it, click F1. The help file find it, that way... and, with
2003, in the IDE, not in the "Access" opening window, the help returned the
same article if I typed DateSerial as keyword I was looking for. Help in
2003 is better than in 2002, on the other hand, that's a fact. I don't have
2002 anymore installed, I can't tell about that version.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Thanks for the quick reply.
I entered "DateSerial" in Access help - and I get [Please rephrase your
question]
This is WinXP SP-2 full install.
Should I be able to look this function up in Access help ?

Matt
Hi,


I don't know what is AYear, I copied it from your message.

It should be DateSerial, not DataSerial. It accepts three argument,
the year, the month, and the day, and return the date that makes that.



Hoping it may help,
Vanderghast, Access MVP



Matt Reed said:
Michel, Thanks for the quick reply.

Your point makes a lot of sense. I liked the way you used feet and
Click to expand...
inches
to
make me see your point. It helped.

When I actually tried to use it I am getting an error.

The expression I entered was:
RunTot: DSum("Freight","Orders","[OrderDate]<=DateSerial( [AYear]
,[AMonth],[ADay])")

The error I got was:
"Microsoft Access can't find the name 'AYear' you used in the
expression.

Question 1 is what did I enter wrong?

More importantly...

Question 2: When I tried to search for DataSerial in access help I get
nothing found or rephrase the question - Is there a more complete help file
available?

Thanks for your help.

Matt

Hi,


The problem is that your test would report that 2 yards 1 foot 8 inches
is not smaller than 6 yards 2 feet 1 inch... Indeed, in your comparison,
you ask about and the inch, and the feet, and the yard, all three,
separately, to be <= , in order to declare the measure <= to the other.
Clearly, that should NOT be tested that way. Ok, you did not use inch, foot
and yard, but day, month and year... but same problem noneless. As the
measure should be compare in ONE step, so the day should. A right criteria
could be like:


" OrderDate <= DateSerial( AYear, AMonth, ADay ) "



a little bit like:


" [Measurement In Inches] <= ConvertIntoInches( Yard, Foot, Inch ) "

would do.



With that system, the running sum starts from the first day in the
table. Add a criteria " AND OrderDate<= SomeDate" to start it at the
supplied SomeDate.


Hoping it may help,
Vanderghast, Access MVP


Matt Reed said:
Hello,
I have been trying to modify the sample as shown in 290136 "How to Create
Running Totals in a Query". Added a column for ADay
DatePart('d',[OrderDate]) and modified the RunTot expression as follows.

RunTot: DSum("Freight","Orders","DatePart('d',[OrderDate])<=" & [ADay] &
"And DatePart'm',[OrderDate])<=" & [AMonth] & " And DatePart('yyyy',
[OrderDate])<=" & [AYear] & "")

I get a daily total, but it starts over at zero everymonth. Where I only
want it to start at zero every year.

Any suggestions on what to change?

Thanks for looking and thanks in advance for any help you can give me.

Matt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top