IIF Syntax Error

[Paul Fenton]

Can someone tell me why I'm getting "Invalid Syntax" with this
statement:

=IIF(IsNull([November]),"N/A",
"[txtNetNov]/DSum("[jobPrice]","tClients","Month([DateSold])=11 And
Year([DateSold])=year(date())-1)")

[November] is a column in a crosstab query of sales volume and for
2005, has no value yet. (Nor does December) What I'm trying to do is
test each month for a value before I calculate the percentage
variation from the previous year. When I have no value in the field
(November and December, currently) I'll get #ERROR if I just try to do
the math, which is:

=Format([txtNetNov]/DSum(@"[job$]","tClients","Month([Date Sold])=11
And Year([Date Sold])=year(date())-1"),"Percent") where [txtNetNov]
is the difference in the sales between that month this year and last
year. If there are no sales in a month, it's returning "Null".

And that works great when there are sales figures for the month.



Paul Fenton

 

[Ken Snell [MVP]]

Not sure what you're trying to get from the IIf test, but you have
mismatched " characters in the "false" argument's string. See the "
character at the beginning of the second line:

=IIF(IsNull([November]),"N/A",
"[txtNetNov]/DSum("[jobPrice]","tClients","Month([DateSold])=11 And
Year([DateSold])=year(date())-1)")

 

[Brendan Reynolds]

There should be no double quote in front of [txtNetNov] and you're missing a
closing parenthesis at the end.

= IIf(IsNull([November]), "N/A", [txtNetNov] / DSum("[jobPrice]",
"tClients", "Month([DateSold])=11 And Year([DateSold])=year(date())-1)"))

 

[Ken Snell [MVP]]

.... and you're missing a closing parenthesis at the end.

Drat... I missed that one. Thanks, Brendan!

 

[Paul Fenton]

Thank you very much gentlemen, you solved my problem.


Paul Fenton