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
|
|||
|
|||
Separate dates in 6 month totals
I have a report that shows all deliveries within a 1 year parameter. In the
footer, I have 2 text boxes that I would like to total the amounts of each product for tax purposes. The first box would total a product from 1 Jan-30Jun, and the second would total the same product from 1 Jul-31Dec of any generic year. I have formatted the date text box as "mm/dd", so I won't have to worry about the year, or change the text box each year. I have tried several expressions in the text box, such as =Sum IIf(([DelDate] Between "01/01" And "07/01"),[Prod1],0) Everything I have tried either gives me an error message or a blank box. Can anyone help with what I need to do? Thanks |
#2
|
|||
|
|||
Separate dates in 6 month totals
1. Create a query using your table.
2. Depress the Total button on the toolbar. Access adds a Total row to the grid. 3. Type an expression like this into a fresh column, in the Field row: Semester: (Month([DelDate]) - 1) \ 6 In the Total row, accept Group By under this field. 4. Add your ProductID field to the grid. Group By. 5. Add the Amount field, and choose Sum in the Total row. For each product, the query returns 2 records: one for semester 0 (Jan - Jun), and the other for semester 1 (Jul - Dec.) It works by getting the month number (1 - 12), subtracting 1 (yielding 0 to 11), and then performing integer division by 6. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jim L." wrote in message ... I have a report that shows all deliveries within a 1 year parameter. In the footer, I have 2 text boxes that I would like to total the amounts of each product for tax purposes. The first box would total a product from 1 Jan-30Jun, and the second would total the same product from 1 Jul-31Dec of any generic year. I have formatted the date text box as "mm/dd", so I won't have to worry about the year, or change the text box each year. I have tried several expressions in the text box, such as =Sum IIf(([DelDate] Between "01/01" And "07/01"),[Prod1],0) Everything I have tried either gives me an error message or a blank box. Can anyone help with what I need to do? Thanks |
Thread Tools | |
Display Modes | |
|
|