Calculated field for option group

[AccessKay]

Would anyone mind taking a guess at what might be missing from this
expression for a calculated field in my query (for the first part of my
option group in an unbound form)?

PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, Sum(IIF(Format([TransDate],
"yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0)

Thank you!

 

[vanderghast]

Missing ) for an expression started by SUM,
Missing a coma and the third argument of the first iif,
Missing ) for the first iif.

PrevQtr: IIF( [Forms]![FrmTest]![ Frame0] = 1,

SUM( IIF( Format([TransDate], "yyyyq")
= Format(DateAdd("q", -1,Date()),
"yyyyq"),
[ODC_Cost],
0
)




Vanderghast, Access MVP

 

[AccessKay]

Thanks for finding this. I'm lousy at writing these. I tried to follow and
now I have too many arguments.

PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, (Sum(IIF(Format([TransDate],
"yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0),0)

Missing ) for an expression started by SUM,
Missing a coma and the third argument of the first iif,
Missing ) for the first iif.

PrevQtr: IIF( [Forms]![FrmTest]![ Frame0] = 1,

SUM( IIF( Format([TransDate], "yyyyq")
= Format(DateAdd("q", -1,Date()),
"yyyyq"),
[ODC_Cost],
0
)




Vanderghast, Access MVP

Would anyone mind taking a guess at what might be missing from this
expression for a calculated field in my query (for the first part of my
option group in an unbound form)?

PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, Sum(IIF(Format([TransDate],
"yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0)

Thank you!

 

[vanderghast]

SUM takes only one argument, not 2 as typed, actually, you have SUM( iif( ,
, ), 0 )



PrevQtr: IIF([Forms]![FrmTest]![ Frame0] = 1,
(Sum( IIF( Format([TransDate], "yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"),
[ODC_Cost],
0
)
, 0
)



You still have an unbalanced number of parentheses: 8 ( for 6 ).



Vanderghast, Access MVP

 

[AccessKay]

I think I got it...
PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, (Sum(IIF(Format([TransDate],
"yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0))),0)

Though, I'm not sure it's what I want...back to making a new one. Thanks
for your assistance. I would have had to stop trying without your help.

SUM takes only one argument, not 2 as typed, actually, you have SUM( iif( ,
, ), 0 )



PrevQtr: IIF([Forms]![FrmTest]![ Frame0] = 1,
(Sum( IIF( Format([TransDate], "yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"),
[ODC_Cost],
0
)
, 0
)



You still have an unbalanced number of parentheses: 8 ( for 6 ).



Vanderghast, Access MVP

Thanks for finding this. I'm lousy at writing these. I tried to follow
and
now I have too many arguments.

PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, (Sum(IIF(Format([TransDate],
"yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0),0)

 

[KARL DEWEY]

What about the leading space in [ Frame0] ?

--
Build a little, test a little.

I think I got it...
PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, (Sum(IIF(Format([TransDate],
"yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0))),0)

Though, I'm not sure it's what I want...back to making a new one. Thanks
for your assistance. I would have had to stop trying without your help.

SUM takes only one argument, not 2 as typed, actually, you have SUM( iif( ,
, ), 0 )



PrevQtr: IIF([Forms]![FrmTest]![ Frame0] = 1,
(Sum( IIF( Format([TransDate], "yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"),
[ODC_Cost],
0
)
, 0
)



You still have an unbalanced number of parentheses: 8 ( for 6 ).



Vanderghast, Access MVP

Thanks for finding this. I'm lousy at writing these. I tried to follow
and
now I have too many arguments.

PrevQtr:IIF([Forms]![FrmTest]![ Frame0] = 1, (Sum(IIF(Format([TransDate],
"yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [ODC_Cost], 0),0)