Calculated field for option group

A

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!
 
V

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
 
A

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)


vanderghast said:
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 said:
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!
 
V

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
 
A

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.


vanderghast said:
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 said:
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)
 
K

KARL DEWEY

What about the leading space in [ Frame0] ?

--
Build a little, test a little.


AccessKay said:
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.


vanderghast said:
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 said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top