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.

cursors question

I'll greatly appreciate any help with the error message bellow. What I am
trying to do is first I am getting a customer type and nr of members of that
type. Next I list first 5 customers and if there are more than 5 I want a
message to appear indicating how many more records are remaining and the las
t
record. Here is what I get:
3 J, Total nr. of records 5
1 R, M
2 H, S
3 S, S
4 W, G
5 W, P
4 O, Total nr. of records 1
1 V, D
5 R, Total nr. of records 17
1 M, M
2 V, J
3 R, R
4 D, K
5 S, M
Server: Msg 16911, Level 16, State 1, Line 39
fetch: The fetch type last cannot be used with forward only cursors.
.. There are 12 more records
-->> The last record being S, S
My code looks something like this:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total nr. of
records ' + cast(@.total as varchar)
---
declare namesList cursor
FOR select surname, name from cust where branch='xxx' and benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) + ', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
IF @.total >= 6
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' + @.fname
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList
Thanks for your help!Hi,
In Order to Use the LAST function you have to declare the Cursor as a
Scroll Cursor.
Please see code below - take note in a slight modification to stop the
Cursor repeating itself.
HTH
Barry
SQL CODE:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by
benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total
nr. of records ' + cast(@.total as varchar)
---
declare namesList scroll cursor -- Declare the Cursor as a Scroll
Cursor
FOR select surname, name from cust where branch='xxx' and
benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) +
', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
--IF @.total >= 6 -- Remove this from here...
WHILE @.@.FETCH_STATUS = 0 And @.Total >= 6 ... and put it here...
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' +
@.fname
Set @.total = 0 --Set The Total to Zero so this loop finishes
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList|||Why use a cursor to do all that formatting rather than just output a
query? Have you looked up the CUBE / ROLLUP operator in Books Online?
It would help you with the total counts.
If you want help to write a query instead of a cursor then please post
DDL and sample data. Cursors are rarely a good idea and surely not for
this - there really are much better ways to write reports.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||hehe!
I was going to maybe post an example to use rather than a cursor -- but
I haven't really got the time!
And without any DDL - I definitely don't have time... ;-)
Barry|||This could be close to what you want but it is untested. It should give
you the first 5 customers per benefit. I've no way of knowing what you
mean by "first" and "last" so I've assumed alphabetical order by name.
You didn't specify ORDER BY so the order you will get from your cursor
is undefined and may be unreliable.
SELECT C.benefit, C.surname, C.name, B.row_cnt, B.last_cust
FROM cust AS C,
(SELECT benefit, COUNT(*) AS row_cnt,
MAX(surname+','+name) AS last_cust
FROM cust
GROUP BY benefit) AS B
WHERE branch='xxx'
AND 5 <=
(SELECT COUNT(*)
FROM cust
WHERE surname+','+name <
C.surname+','+C.name)
AND B.benefit = C.benefit
ORDER BY C.benefit, C.surname, C.name ;
The pretty formatting you can do at the front end or in a reporting
tool.
Hope this helps.
David Portas
SQL Server MVP
--|||Correction. Change "<=" to ">="
David Portas
SQL Server MVP
--