Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Function in Report

    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?

    Thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    361
    I tired to same function based off the table and that failed. I guess it will have to come from the query.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Before you give up....

    Hi

    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 can’t 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).

    Cheers

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    =DSum(“[Excess Mileage]”, “[tbl Main]”, “[Date Settled] Between #9/1/2003# And #9/30/2003#”)

    That is the function that I wrote and I get an error. Invaild Syntax. Opertand or Operator.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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#")

  6. #6
    Join Date
    Mar 2004
    Posts
    361
    Sweet Georgia Brown. That works. Thanks so much for the help. Look forward to talking with you in the future.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure. Glad to have finally been some use. Speak again soon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •