Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    18

    Unanswered: Trying to create a joint IIF and SUM formula

    [Using Access 2003 with XP Pro]

    I have a query that has 5 fields: Criteria (text), C1, C2, R1, R2 (remaining fields for are always either NULL or numeric values)

    We'll make the first record: C1=4, C2=4, R1=8, R2=8

    I'm trying to create a new field that uses an IFF criteria then adds C1 & C2 together or R1 & R2 together accordingly, but only at each individual record level.

    I've kicked around a few formulas to no avail:

    Total: IIf([Criteria]="Comm",[C1]+[C2],[R1]+[R2]), results in 44, not the actual sum of 8

    Total LT: IIf([modCategory]="Commercial",Sum([COMM Admin]+[COMM LT]),Sum([RA Admin]+[RA LT])), crashes with "You tried to execute a query that does not include the specified expression 'No' as part of an aggregate function."

    I feel dumb asking these questions, but I don't have any colleagues that are capable and/or able to assist me. I've been surfing around a few websites / forums as well with no luck...

    Please help me.

    Thanks a MILLON!

    Malvaro

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Look at what the error message is telling you...

    You're apparently using a GROUP BY clause that does not include "no"...

    Or, alternately, you called an aggregate function and didn't include a GROUP BY clause at all...
    Last edited by Teddy; 11-25-08 at 14:27.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by Teddy
    Look at what the error message is telling you...

    You're apparently using a GROUP BY clause that does not include "no"...

    Or, alternately, you called an aggregate function and didn't include a GROUP BY clause at all...
    a) Not in this query or the query it's pulling data from...

    b) I'm not exactly sure what your implying, though I do know that "SUM" is a aggregate function... clarification?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    LOL! 44 is correct!!

    Your fields are text and "4" + "4" is "44".

    If you intend to calculate on a field, then make the field numeric.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by StarTrekker
    LOL! 44 is correct!!

    Your fields are text and "4" + "4" is "44".

    If you intend to calculate on a field, then make the field numeric.
    The format of all five fields is already General Number...

    This isn't supposed to be rocket-science. Why isn't this simple formula working the way I want it to?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The format of all five fields is already General Number...
    Nobody ever mentioned "format"... check your DATA TYPES.

    This isn't supposed to be rocket-science. Why isn't this simple formula working the way I want it to?
    Because you screwed up on basic stuff like setting correct data types for your fields. It has nothing to do with rocket science but plenty to do with database design fundamentals.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by StarTrekker
    Nobody ever mentioned "format"... check your DATA TYPES.

    Because you screwed up on basic stuff like setting correct data types for your fields. It has nothing to do with rocket science but plenty to do with database design fundamentals.
    I like your sense of sarcasm / humor hehehe

    Perhaps I wasn't clear enough with my follow-up observations:

    C1, C1, R1, R2, Total are all new fields created from within my query...

    C1, C1, R1, R2 are all Networkdays (3rd party module) calculations referencing date fields from another source SQL table... exa: C1: networkdays([Start],[End])... resulting in 4

    So unless I'm again mistaken, while in Design view for the query, I've set all these query fields C1, C1, R1, R2, Total to the format style "general number" in the Field Properties area... can i directly change data types in a query?

    I certainly wouldn't put it past myself that I'm somewhere mistaken in my database design fundamentals... I'm more of an Excel guy thrown to the wolves...

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ Sometimes my sarcasm is taken totally the wrong way though!

    Instead of [C1]+[C2] you can force data types to, say, Double, with CDbl([C1])+CDbl([C2]). Give that a go

    If you still get no joy, you might have to resolve them. Instead of [C1], use the same expression that generates C1... in your case networkdays([Start],[End]) or CDbl(networkdays([Start],[End])).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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