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
|
|||
|
|||
Finding Totals from two diff date ranges
I have a form that includes two sub forms that are link together. subformA
and SubformB. SubformA is linked to the Parent form FormA and SubformB is linked to SubformA. When I select from the list of items in subformA the the proper list shows in subformB ok. the problem is that the detail list in subformB has a date fiield that reflects dates from 2009 and 2008 and a Dollar figure for each date called the amount field. I want to show a total in the parent form formA to reflect the totals for 2008 and 2009 in two different fields on formA. Example SubformA MeterNumber SupplierName 4446900 Pitney Bowes SubFormB Date Amount 07/01/2009 3000 04/22/2009 3000 01/22/2009 3000 11/07/2008 3000 08/22/2008 3000 06/11/2008 42 05/27/2008 3000 03/18/2008 3000 I to show in the main form Total 2009: $9,000.00 Total 2008: $12,042.00 Right now it works but it shows the sum for both Total 2009: 21,042.00 I don't have a field set for 2008 yet. -- Dave K. -- Dave K. |
#2
|
|||
|
|||
Finding Totals from two diff date ranges
On Thu, 17 Sep 2009 05:37:07 -0700, Dave K.
wrote: I have a form that includes two sub forms that are link together. subformA and SubformB. SubformA is linked to the Parent form FormA and SubformB is linked to SubformA. When I select from the list of items in subformA the the proper list shows in subformB ok. the problem is that the detail list in subformB has a date fiield that reflects dates from 2009 and 2008 and a Dollar figure for each date called the amount field. I want to show a total in the parent form formA to reflect the totals for 2008 and 2009 in two different fields on formA. Example SubformA MeterNumber SupplierName 4446900 Pitney Bowes SubFormB Date Amount 07/01/2009 3000 04/22/2009 3000 01/22/2009 3000 11/07/2008 3000 08/22/2008 3000 06/11/2008 42 05/27/2008 3000 03/18/2008 3000 I to show in the main form Total 2009: $9,000.00 Total 2008: $12,042.00 Right now it works but it shows the sum for both Total 2009: 21,042.00 I don't have a field set for 2008 yet. When it gets to be 2013, will you want fields for 2008, 2009, 2010, 2011, 2012 and 2013? I.e. will you keep changing the design of the form? Ouch! I'd suggest not getting the data from the Subform; the data isn't IN the subform, it's in a table. Instead, you could use the DSum() function as the control source of a textbox on the main form. If you want to get the sum for the current year in one textbox and the previous year in a second textbox, you could use a Control Source like =DSum("[Amount]", "[tablename]", "[Date] = DateSerial(Year(Date()), 1, 1) AND [Date] DateSerial(Year(Date()) + 1, 1, 1)") for the current year, and =DSum("[Amount]", "[tablename]", "[Date] = DateSerial(Year(Date())-1, 1, 1) AND [Date] DateSerial(Year(Date()), 1, 1)") for the previous. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Finding Totals from two diff date ranges
John, That works grea, but it gives me the total for all the items in the
table (meter) I need the same thing but for each meter I select in the sub-form. This is what I have =DSum("[amount]","[TblPostage]","[date]= DateSerial (2009, 1, 1) and [date]Dateserial(2009+1,1,1)") Dave -- Dave K. "John W. Vinson" wrote: On Thu, 17 Sep 2009 05:37:07 -0700, Dave K. wrote: I have a form that includes two sub forms that are link together. subformA and SubformB. SubformA is linked to the Parent form FormA and SubformB is linked to SubformA. When I select from the list of items in subformA the the proper list shows in subformB ok. the problem is that the detail list in subformB has a date fiield that reflects dates from 2009 and 2008 and a Dollar figure for each date called the amount field. I want to show a total in the parent form formA to reflect the totals for 2008 and 2009 in two different fields on formA. Example SubformA MeterNumber SupplierName 4446900 Pitney Bowes SubFormB Date Amount 07/01/2009 3000 04/22/2009 3000 01/22/2009 3000 11/07/2008 3000 08/22/2008 3000 06/11/2008 42 05/27/2008 3000 03/18/2008 3000 I to show in the main form Total 2009: $9,000.00 Total 2008: $12,042.00 Right now it works but it shows the sum for both Total 2009: 21,042.00 I don't have a field set for 2008 yet. When it gets to be 2013, will you want fields for 2008, 2009, 2010, 2011, 2012 and 2013? I.e. will you keep changing the design of the form? Ouch! I'd suggest not getting the data from the Subform; the data isn't IN the subform, it's in a table. Instead, you could use the DSum() function as the control source of a textbox on the main form. If you want to get the sum for the current year in one textbox and the previous year in a second textbox, you could use a Control Source like =DSum("[Amount]", "[tablename]", "[Date] = DateSerial(Year(Date()), 1, 1) AND [Date] DateSerial(Year(Date()) + 1, 1, 1)") for the current year, and =DSum("[Amount]", "[tablename]", "[Date] = DateSerial(Year(Date())-1, 1, 1) AND [Date] DateSerial(Year(Date()), 1, 1)") for the previous. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Finding Totals from two diff date ranges
On Thu, 17 Sep 2009 11:40:04 -0700, Dave K.
wrote: John, That works grea, but it gives me the total for all the items in the table (meter) I need the same thing but for each meter I select in the sub-form. This is what I have =DSum("[amount]","[TblPostage]","[date]= DateSerial (2009, 1, 1) and [date]Dateserial(2009+1,1,1)") Dave Add a criterion for the meter to the third argument to DSum. Since I have no idea where or how the meter information is stored I can't be specific, but the third argument is a valid SQL WHERE clause without the WHERE; try creating a query that retrieves the items you want, view it in SQL view, and use that as the basis for your DSum. Do note that your query with 2009 literally in it will give 2009 data next year, and year after, and forever... unlike using Year(Date()), which is what I suggested. If that's the result you want, fine. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Finding Totals from two diff date ranges
John I thing I have it right now, but the text box with this control :
=DSum("[amount]","[TblPostage]","[meter Number]= [MasterSubform].Form![TblEquipment Subform].Form![MeterNumber] And [date]= DateSerial (2009, 1, 1) and [date]Dateserial(2009+1,1,1) ") will not update when I select a new record in the subform. The Meter information is stored in one table, the office where each meter is located is in another table and the postage for each meter is recorded in another table. I have a main form that shows the office location and when each office location changes the subform reflects the meters that go to that office and in a second subform it shows the detail for each meter when it is selected in the meter sub form. hope this helps. -- Dave K. "John W. Vinson" wrote: On Thu, 17 Sep 2009 11:40:04 -0700, Dave K. wrote: John, That works grea, but it gives me the total for all the items in the table (meter) I need the same thing but for each meter I select in the sub-form. This is what I have =DSum("[amount]","[TblPostage]","[date]= DateSerial (2009, 1, 1) and [date]Dateserial(2009+1,1,1)") Dave Add a criterion for the meter to the third argument to DSum. Since I have no idea where or how the meter information is stored I can't be specific, but the third argument is a valid SQL WHERE clause without the WHERE; try creating a query that retrieves the items you want, view it in SQL view, and use that as the basis for your DSum. Do note that your query with 2009 literally in it will give 2009 data next year, and year after, and forever... unlike using Year(Date()), which is what I suggested. If that's the result you want, fine. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Finding Totals from two diff date ranges
On Fri, 18 Sep 2009 05:59:02 -0700, Dave K.
wrote: =DSum("[amount]","[TblPostage]","[meter Number]= [MasterSubform].Form![TblEquipment Subform].Form![MeterNumber] And [date]= DateSerial (2009, 1, 1) and [date]Dateserial(2009+1,1,1) ") will not update when I select a new record in the subform. Requery the textbox in the Current event of the subform. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|