Separate dates in 6 month totals

[Jim L.]

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

 

[Allen Browne]

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.