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...
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...