IIF Syntax Error

  • Thread starter Thread starter Paul Fenton
  • Start date Start date
P

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
 
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)")
 
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)"))
 
Back
Top