Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum of values in a report (access2000)

    I have a text box (txtProjecthours) on a report that is calculating a representing avalue from a supporting query...

    =(IIf([number]=27,[027 project hours],IIf([number]=28,[028 project hours],IIf([number]=31,[031 project hours],[032 project hours])))

    i need now to provide a sum of this in a month footer, a project footer and the report footer but when i create a text box and enter

    =Sum(IIf([number]=27,[027 project hours],IIf([number]=28,[028 project hours],IIf([number]=31,[031 project hours],[032 project hours])))) <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    i get an error of "DATA TYPE MISMATCH IN CRITERIA EXPRESSION"

    i have tried
    =CInt(IIf([number]=27,[027 project hours],IIf([number]=28,[028 project hours],IIf([number]=31,[031 project hours],[032 project hours])))) in the detail and <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    =sum(CInt(IIf([number]=27,[027 project hours],IIf([number]=28,[028 project hours],IIf([number]=31,[031 project hours],[032 project hours]))))) in the footer but still get the same error. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    i have also tried changing the field properties in the supporting query but they seem to be all set to general number, which i would expect to work. <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    dont panic if the parenthesis are incorrect above - pasting error only - the syntax of the database functions are correct. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

    can anyone help please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum of values in a report (access2000)

    I would add the complex expression to the query that acts as reocrd source of the report as a calculated field ProjectHours.

    Then, you can change the control source of txtProjectHours to ProjectHours, and the control source of the text boxes in the footer sections to =Sum([ProjectHours])

    Note: you can use the Switch function instead of nested IIf's. It doesn't make the expression shorter, but it's easier to read.

    Project Hours:Switch([Number]=27,[027 project hours],[Number]=28,[028 project hours],[Number]=31,[031 project hours],[Number]=32,[032 project hours])

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in a report (access2000)

    Hans, thanks

    Switch is great, i was looking for the equivalent of Select Case but could find it <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    putting the expression in the query gives the same error, even without the sum(......)

    i am completely lost now <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>, what can you suggest?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum of values in a report (access2000)

    Are [027 project hours] etc. available in the query? If so, are they numeric fields?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in a report (access2000)

    Ok, it could get complex here, i will try my best.

    there is a table (tblHitorical) that has the hours that someone works on a project, a query is based on this table and that calculates the hours that each person worked on these specific projects. it returns , among others, the four fields 027 project hours, 028 project hours, 031 project hours, 032 project hours. using this...

    027 Project Hours: Sum(IIf(Right([number],3) In (27),[hours],0)) - i know i dont need 'in' but tat is a relic of me trying to solve another issue that stopped the report working as desired.
    the table field [hours] is numeric.

    this report is combined with other reports into one huge query that is then referenced by the report.
    the report places the project hours data into the detail using...
    =Switch([number]=27,[027 project hours],[number]=28,[028 project hours],[number]=31,[031 project hours],[number]=32,[032 project hours])
    and works accurately

    this is what i need to total into the month, project and report footers.

    I also need to calculate the cost per project across the detail area. using the calculation ...
    ([total cost]/[total hours]) * (Switch([number]=27,[027 project hours],[number]=28,[028 project hours],[number]=31,[031 project hours],[number]=32,[032 project hours]))
    this works quite happily in the detail area, i aim to sum this in the footers in a similar way to the project hours (if i ever get that far).

    i dont understand why the figures are used in the math across the page but not down the page, out of the detail area

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum of values in a report (access2000)

    I'm sorry, I have no idea...

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sum of values in a report (access2000)

    Post your query here and we can have a look at it. Is [number] numeric, the reason I ask is the Right([number],3) code you have?
    I would also change your labelling of these amounts and not have the 027 etc at the front of the label (just my preference), it may not be causing problems but then who knows.

    Pat

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum of values in a report (access2000)

    So is [number] a text field that holds something like "027"? Because if it isn't, then you don't need <font color=blue>027 Project Hours: Sum(IIf(Right([number],3) In (27),[hours],0))</font color=blue> at all. You can just use this: 027 Project Hours: Sum(IIf([number]=27,[hours],0)). If [number] *is* a text field, then your Switch won't work properly because you're doing this: <font color=blue> ...[number]=27,[027 project hours] ...</font color=blue>, which assumes [number] is numeric.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in a report (access2000)

    [number] is a numeric field in one of the tables. it is 6 characters long - the first three digits represent the department and the last three represent the project code.

    the Switch function works properly.

    I did have an iif statement in the query, along the lines of what you suggest but that led to grouping problems when i wanted to return the project code (right([number],3) in the query - the totals in other columns were then changed see
    Query Hell (post no238856)

    is there anyway in VB to get at the values ? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum of values in a report (access2000)

    If it is a text field that holds numbers, that doesn't make it a numeric field. You can't have leading zeroes in a numeric field, only in a text field. You can always use a conversion function: CInt(), Val(), etc.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in a report (access2000)

    still working on the problem and have come up with this 'theoretical' solution <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>.

    i have the report as is and the supporting query provides 4 fields - 027 project hours , 028 project hours , 031 project hours , 032 project hours. i filter these into one field with the SWITCH function according to the group that the data is being reported under.

    currently i have trouble getting that detail value to sum in the month and project footers. if i put =sum([027 project hours]) as the control source of a text box then that gives me the sum for that project ijn whatever footer i put it into. If i put four of these controls in each footer all i need do is stop three of them from showing according to which footer they are in. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    something like if [number] = 027 then txt028projecthours.visible = false.

    how do i refer to the objects in the report and what event should i put the code in? i have found the GroupFooter1_Format(Cancel As Integer, FormatCount As Integer) event but am unsure if this is correct and how to get to the objects that i need to hide.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum of values in a report (access2000)

    The On Format event of the group footer is the one you want. You must refer to the text boxes by their names. You can check those in the third tab of the Properties window.

    But it's still not clear what "Number" is. You have stated that it is numeric, but also that it is 6 characters long. Like Charlotte, this makes me suspect that it is a text field (even though it contains numeric-looking values).

    If it is a text field, you should put quotes around its values. [number] = 027 doesn't make sense, in particular if all six characters are important. It may have to be something like

    [txt027ProjectHours].Visible = (Right([number], 3) = "027")
    [txt028ProjectHours].Visible = (Right([number], 3) = "028")
    [txt031ProjectHours].Visible = (Right([number], 3) = "031")
    [txt032ProjectHours].Visible = (Right([number], 3) = "032")

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in a report (access2000)

    Hans / Charlotte,

    thanks for your thoughts on this topic. using the code above - butchered slightly to take in my textbox names etc - the report is working as required.
    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21> <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25> <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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