Create A Unique Number for Reference of Invoice 1

[ciscowiz]

I am using SQL Server 2000 and CR9. While we do store Invoice Numbers in the DB and we have User Functions to Auto-Generate Numbers, I need this done in CR.

I basically Just want to use the parameter, which is a date, and append an incremented number to the end of it. The Number needs to show up in the Group Header and Group Footer. Here is what I have:
//@Counter
Whileprintingrecords;

numbervar Invoice:= Invoice + 1

AND

//@InvNum
DatePart ("m",{?@StartDate} ) & DatePart ("y",{?@StartDate} ) & DatePart ("yyyy",{?@StartDate} )
& "-" & {@Count}

So it would look like: 112006-1, 112006-2, 112006-3, etc.
The problem I am having is the group footer increments the counter so I end up with 112006-1 in the Group Header and 112006-2 in the group footer. Obviously I need it to be the same number.

Thanks,
Bryan

 

[lbass]

Set up your formula like this for the group header:

Whileprintingrecords;
numbervar cnt:= cnt + 1;
stringvar start := totext({?StartDate},"Mdyyyy");
stringvar invno := start + "-" + cnt;

Then in the group footer, just reference it:

whileprintingrecords;
stringvar invno;

-LB

 

[ciscowiz]

Thank you LB, once again you have come through. I really hope they pay you for this.

Only thing I had to do was a Cstr on the 'cnt' for the 'invno' variable. Thanks again!

Bryan

 

[lbass]

Oops, yes, I didn't type that out correctly.

-LB

 

[ciscowiz]

LB - When I view the report in the designer, the formula shows up correctly (I changed the date order): 20060101-1

However, If I view the report over the web, through my custom app it shows up as 20060101-1.00.

Any idea why it is adding the 2 decimal places? If I just put the "counter" formula on the page it shows up as '1'. I tried formatting the field but there is no option for numbers.

Thanks,
Bryan

 

[lbass]

Instead of cstr(cnt), use:

totext(cnt,0,"")

-LB

 

[ciscowiz]

Thanks again LB!