conditional control source property

[Guest]

My Office 2003 report control source is a query that includes fields for two
dates. I have a text box in group footer with the following statement as the
data source:

=iif([date1]<[date2], [date2], null)

I have tried this statement with various return values such as "false" for
null and "true" for [date2], but the statement always returns an error.

The error drop down shows "Invalid Control property: Control Source", and
"Circular Reference", but neither makes sense to me.

Also, a statement in another text box with two IIF contructs generates an
error indicating the wrong numbe or agruments, e.g. iif(c>d, a,iif(b<0,0,b)),
where a, b, c and d are fields in the report data source. Is this due to an
inherent limitation in Access?

Thanks,

DM

 

[Marshall Barton]

My Office 2003 report control source is a query that includes fields for two
dates. I have a text box in group footer with the following statement as the
data source:

=iif([date1]<[date2], [date2], null)

I have tried this statement with various return values such as "false" for
null and "true" for [date2], but the statement always returns an error.

The error drop down shows "Invalid Control property: Control Source", and
"Circular Reference", but neither makes sense to me.

Also, a statement in another text box with two IIF contructs generates an
error indicating the wrong numbe or agruments, e.g. iif(c>d, a,iif(b<0,0,b)),
where a, b, c and d are fields in the report data source. Is this due to an
inherent limitation in Access?

The first IIf looks fine, just make sure the name of the
text box is NOT date1 or date2. If it is either of those,
it would be a circular reference (i.e. the text box would
have to evaluate itself to evaluate the expression).

I don't see how what you posted for the second IIf could
possibly give a too many argumants error. If you typed the
function in your post, then go back and double check the
original control source expression and next time use
Copy/Paste to post a troublesome expression instead of
asking us to debug a typo or something that is different
than the original. If the original really is OK, then I
guess you shuold look somewhere else for the cause of the
error.

 

[Guest]

Thanks, first problem solved. re: the second, the actual expression is:

=iif([curperend]<[guaranteeend],Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]+[balance], iif(Sum([adj
comm])-([curpertalent]*0.15)-[regdraw])<0,0, Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]))

Error message is "The expression...has a function containing the wrong
number of arguments."

DM

My Office 2003 report control source is a query that includes fields for two
dates. I have a text box in group footer with the following statement as the
data source:

=iif([date1]<[date2], [date2], null)

I have tried this statement with various return values such as "false" for
null and "true" for [date2], but the statement always returns an error.

The error drop down shows "Invalid Control property: Control Source", and
"Circular Reference", but neither makes sense to me.

Also, a statement in another text box with two IIF contructs generates an
error indicating the wrong numbe or agruments, e.g. iif(c>d, a,iif(b<0,0,b)),
where a, b, c and d are fields in the report data source. Is this due to an
inherent limitation in Access?

The first IIf looks fine, just make sure the name of the
text box is NOT date1 or date2. If it is either of those,
it would be a circular reference (i.e. the text box would
have to evaluate itself to evaluate the expression).

I don't see how what you posted for the second IIf could
possibly give a too many argumants error. If you typed the
function in your post, then go back and double check the
original control source expression and next time use
Copy/Paste to post a troublesome expression instead of
asking us to debug a typo or something that is different
than the original. If the original really is OK, then I
guess you shuold look somewhere else for the cause of the
error.

 

[Marshall Barton]

Thanks, first problem solved. re: the second, the actual expression is:

=iif([curperend]<[guaranteeend],Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]+[balance], iif(Sum([adj
comm])-([curpertalent]*0.15)-[regdraw])<0,0, Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]))

Error message is "The expression...has a function containing the wrong
number of arguments."

I might be going cross eyed, but I still don't see anything
wrong with it. Maybe you can cut out pieces of it to try to
isolate the part that it's complaining about.

One clue could be that the iif was not changed to IIf, as if
it thought you had a UDF named iif. Or, maybe there's a
library reference problem.

 

[Guest]

That's a good point (about the "iif"). In the end I decided to go with a
temporary summary table with all calcs done linked to the report data source
qry.

DM

Thanks, first problem solved. re: the second, the actual expression is:

=iif([curperend]<[guaranteeend],Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]+[balance], iif(Sum([adj
comm])-([curpertalent]*0.15)-[regdraw])<0,0, Sum([adj
comm])-([curpertalent]*0.15)-[regdraw]))

Error message is "The expression...has a function containing the wrong
number of arguments."

I might be going cross eyed, but I still don't see anything
wrong with it. Maybe you can cut out pieces of it to try to
isolate the part that it's complaining about.

One clue could be that the iif was not changed to IIf, as if
it thought you had a UDF named iif. Or, maybe there's a
library reference problem.