Hey Guys, nice new design on the website. Anyways I have a question about a report. Here is the function I have:
=IIf([Date Settled] Between #9/1/2003# And #9/30/2003#,Sum([Excess Mileage]))
So, If the date falls b/w x and y I want the function to sum the numbers in the excess mileage column of the table.
Do I have to tie this to a query or can I just get the info off the table? Right now I have tied it to a query but I need to sum the mileage for the while year, month by month. The query says Fieldate Settled Criteria: B/W X and y. The next field is excess mileage. What Shall I Do?
Could you pop a Group Header of =format([date settled], "mmmm") in and place a text box with =sum([excess mileage]) in that?
Or, if you cant group by month but still need a monthly value, could a =DSum([excess mileage], [YourTable], [Date Settled] Between #9/1/2003# And #9/30/2003#) be used? If you can be bothered, making the where part of the DSum more like format([date settled], "mm") = & txtMnth , where txtMnth is an incrementing value up to 12 will help future proof the report (I speak from bitter experience after having had to revise a whole load of reports at the end of the financial year).
Yeah, my mistake. Typed it up in word for that well spelled look, which meant it used curly speech marks. The below is your code pasted into a text field (got you oper* errors) and then fixed the quotation marks. Worked on a table with a few random figures.
=DSum("[Excess Mileage]","[tbl Main]","[Date Settled] Between #9/1/2003# And #9/30/2003#")