Friday, February 24, 2012

Custom Code error with VB function

Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:

Public Function funAdditions(pFields As Fields) As Double
if pFields !FA00902_AMOUNT.Value > 0 and
pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
End Function

...and after much research cannot figure out the solution to this error:

[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.

I'm new to SSRS so is there a syntax error I'm missing?

Thanks in advance,

Buster

Just a thought, and it may be that your code has been munged by pasting it into here, but you seem to have your code lines split over several physical lines, to do that in VB and VBScript you need to use the line continuation character, which is an underscore. So your first line should look something like this:

Public Function funAdditions(pFields As Fields) As Double
if pFields !FA00902_AMOUNT.Value > 0 and _
pFields !FA00902_TRANSACCTTYPE.Value = 3 and _
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and _
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT _
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then

return pFields !FA00902_AMOUNT.Value

...etc...

Sometimes it can be handy to fire up the macro code editor that you get with Excel and Word and do your code in that to make sure it is syntactically correct.

No comments:

Post a Comment