Month to Date information

[DaS]

I am using a table that has the Date as the Primary key. I have built a
userform that shows the various related Revenues that occured on that date,
but I would like to show Month-to-Date information on that as well, i.e., if
the current date being viewed is 20 July 2007, I would like the month-to-date
add up all of the revenues from 01 July through 20 July. If I'm viewing 31
Aug 2007, I would like the revenues to add up 01-31 August. I am very new to
Access,(so new, in fact, I have the "Dummy Book"), so dumb it down if you
can. Thanks so much!

 

[KARL DEWEY]

This will give you all between the first of the month and today --
Between (Date()-Day(Date())+1 AND Date()

 

[John W. Vinson]

I am using a table that has the Date as the Primary key.

Just be sure that you do NOT use Date as the fieldname. It's a reserved word
for the builtin Date() function (which gets today's date from the computer
clock).

I have built a
userform that shows the various related Revenues that occured on that date,
but I would like to show Month-to-Date information on that as well, i.e., if
the current date being viewed is 20 July 2007, I would like the month-to-date
add up all of the revenues from 01 July through 20 July. If I'm viewing 31
Aug 2007, I would like the revenues to add up 01-31 August. I am very new to
Access,(so new, in fact, I have the "Dummy Book"), so dumb it down if you
can. Thanks so much!

You want just one textbox on the form to show the sum of all the revenues for
that month, along with the details of the individual items? If so, put a
textbox (on the form's header or footer is convenient) with a control source
like

=DSum("[Revenue]", "[Tablename]", "[Datefield] >= #" &
DateSerial(Year(Date()), Month(Date()), 1) & "#")

DateSerial is a builtin function which takes three numeric arguments, year,
month, and day, and constructs a Date/Time value; # is a required date
delimiter; and DSum is another builtin function which will return the sum of
the values in the field named in the first argument ([Revenue] here) in the
table named in the second argument (use your own table name in place of
Tablename), filtered by the criteria specified in the third argument.

John W. Vinson [MVP]

 

[DaS]

I'm not using "DATE" as the name. One question though, with this formula:
Between (Date()-Day(Date())+1 AND Date()

Where to I insert it into the userform? Each day has 12 various fields for
revenue, that does work for each day as I have it set up now. So how do I
use the above in an arrangment? Do I need to make an expression somehow?
Possibly using an if/then statment as pertaining to the [Day] or the
applicable [field name]?

I am using a table that has the Date as the Primary key.

Just be sure that you do NOT use Date as the fieldname. It's a reserved word
for the builtin Date() function (which gets today's date from the computer
clock).

I have built a
userform that shows the various related Revenues that occured on that date,
but I would like to show Month-to-Date information on that as well, i.e., if
the current date being viewed is 20 July 2007, I would like the month-to-date
add up all of the revenues from 01 July through 20 July. If I'm viewing 31
Aug 2007, I would like the revenues to add up 01-31 August. I am very new to
Access,(so new, in fact, I have the "Dummy Book"), so dumb it down if you
can. Thanks so much!

You want just one textbox on the form to show the sum of all the revenues for
that month, along with the details of the individual items? If so, put a
textbox (on the form's header or footer is convenient) with a control source
like

=DSum("[Revenue]", "[Tablename]", "[Datefield] >= #" &
DateSerial(Year(Date()), Month(Date()), 1) & "#")

DateSerial is a builtin function which takes three numeric arguments, year,
month, and day, and constructs a Date/Time value; # is a required date
delimiter; and DSum is another builtin function which will return the sum of
the values in the field named in the first argument ([Revenue] here) in the
table named in the second argument (use your own table name in place of
Tablename), filtered by the criteria specified in the third argument.

John W. Vinson [MVP]

 

[John W. Vinson]

I'm not using "DATE" as the name. One question though, with this formula:
Between (Date()-Day(Date())+1 AND Date()

That was Karl's suggestion, not mine.

Where to I insert it into the userform?

You don't. You insert it on the Criteria line in the Query upon which the form
is based.

Each day has 12 various fields for
revenue, that does work for each day as I have it set up now. So how do I
use the above in an arrangment? Do I need to make an expression somehow?
Possibly using an if/then statment as pertaining to the [Day] or the
applicable [field name]?

Since I have no idea how your table is structured, what the [Day] might be
(nobody here has suggested that), or what these twelve fields might be, I
cannot make any useful suggestions. What are the fieldnames, datatypes and
meanings of the fields in your table? Which is the primary key?

John W. Vinson [MVP]

 

[DaS]

I am using the Date as the primary key [day]. Each field is simply different
outlets for revenue. They are all in a currency format.

I'm not using "DATE" as the name. One question though, with this formula:
Between (Date()-Day(Date())+1 AND Date()

That was Karl's suggestion, not mine.

Where to I insert it into the userform?

You don't. You insert it on the Criteria line in the Query upon which the form
is based.

Each day has 12 various fields for
revenue, that does work for each day as I have it set up now. So how do I
use the above in an arrangment? Do I need to make an expression somehow?
Possibly using an if/then statment as pertaining to the [Day] or the
applicable [field name]?

Since I have no idea how your table is structured, what the [Day] might be
(nobody here has suggested that), or what these twelve fields might be, I
cannot make any useful suggestions. What are the fieldnames, datatypes and
meanings of the fields in your table? Which is the primary key?

John W. Vinson [MVP]

 

[John W. Vinson]

I am using the Date as the primary key [day]. Each field is simply different
outlets for revenue. They are all in a currency format.

"They". You haven't said what "they" are, or anything about the structure of
your table. I'm GUESSING that you are "committing spreadsheet", with data
("outlets" whatever an outlet is, I don't know) in fieldnames.

Did you try my suggestion from earlier in the thread? On the Criteria line
under Day (which is, unfortunately, *another* reserved word) use

= DateSerial(Year(Date()), Month(Date()), 1) AND <= DateAdd("d", 1, Date())

to get all records between the first of the current month and the upcoming
midnight.

John W. Vinson [MVP]

 

[DaS]

Yes I did use that formula under the Criteria and it did work, to an extent.
It is totaling the Outlet, which is an individual department within a large
group, but it's only totaling this (October) current month. In the form, one
can scroll back through all of the days back to November of 2006, with the
revenues from each outlet displaying accordingly. What I'm trying to do, is
as one scrolls through October, it lists the Month to Date total for October.
If one scrolls back to September, it will then display the Month to date for
September. Again, thank you so much for your input on this matter.

I am using the Date as the primary key [day]. Each field is simply different
outlets for revenue. They are all in a currency format.

"They". You haven't said what "they" are, or anything about the structure of
your table. I'm GUESSING that you are "committing spreadsheet", with data
("outlets" whatever an outlet is, I don't know) in fieldnames.

Did you try my suggestion from earlier in the thread? On the Criteria line
under Day (which is, unfortunately, *another* reserved word) use

= DateSerial(Year(Date()), Month(Date()), 1) AND <= DateAdd("d", 1, Date())

to get all records between the first of the current month and the upcoming
midnight.

John W. Vinson [MVP]

 

[John W. Vinson]

Yes I did use that formula under the Criteria and it did work, to an extent.
It is totaling the Outlet, which is an individual department within a large
group, but it's only totaling this (October) current month. In the form, one
can scroll back through all of the days back to November of 2006, with the
revenues from each outlet displaying accordingly. What I'm trying to do, is
as one scrolls through October, it lists the Month to Date total for October.
If one scrolls back to September, it will then display the Month to date for
September. Again, thank you so much for your input on this matter.

What's the Recordsource of the form? Do you have a form with a subform or
what?

John W. Vinson [MVP]

 

[DaS]

Its an XL file that's been imported into a table. There is no subform with
this, I'm not that advanced with Access yet.

 

[John W. Vinson]

Its an XL file that's been imported into a table. There is no subform with
this, I'm not that advanced with Access yet.

You'll need to advance, then. You cannot calculate values like this in a
Table. Tables are for data storage, not for calculations or data display.

Base a Form on your table and do the calculation of month to date in a textbox
on the Form.

John W. Vinson [MVP]

 

[DaS]

Right. That's what I'm trying to do, but the calcualtion in the text box
only returns the total for October 2007. It doesn't change when I scroll to
prior months. That's what I'm trying to figure out. When the date on the
userform changes back to a prior month, how can I get the monthly outlet
revenue totals for that particular month being veiwed. Thanks again John.