If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Month to Date information
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! |
#2
|
|||
|
|||
Month to Date information
This will give you all between the first of the month and today --
Between (Date()-Day(Date())+1 AND Date() -- KARL DEWEY Build a little - Test a little "DaS" wrote: 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! |
#3
|
|||
|
|||
Month to Date information
On Mon, 22 Oct 2007 06:45:04 -0700, DaS wrote:
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] |
#4
|
|||
|
|||
Month to Date information
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]? "John W. Vinson" wrote: On Mon, 22 Oct 2007 06:45:04 -0700, DaS wrote: 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] |
#5
|
|||
|
|||
Month to Date information
On Mon, 22 Oct 2007 13:51:03 -0700, DaS wrote:
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] |
#6
|
|||
|
|||
Month to Date information
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. "John W. Vinson" wrote: On Mon, 22 Oct 2007 13:51:03 -0700, DaS wrote: 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] |
#7
|
|||
|
|||
Month to Date information
On Tue, 23 Oct 2007 05:14:01 -0700, DaS wrote:
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] |
#8
|
|||
|
|||
Month to Date information
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. "John W. Vinson" wrote: On Tue, 23 Oct 2007 05:14:01 -0700, DaS wrote: 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] |
#9
|
|||
|
|||
Month to Date information
On Tue, 23 Oct 2007 13:48:00 -0700, DaS wrote:
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] |
#10
|
|||
|
|||
Month to Date information
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" wrote: On Tue, 23 Oct 2007 13:48:00 -0700, DaS wrote: 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] |
|
Thread Tools | |
Display Modes | |
|
|