Crystal reports and vb.net

JAM1011
JAM1011 used Ask the Experts™
on
Hey all,

I am very new to crystal reports and really need to solve problem as soon as possible.
I am trying to pull value in from an access database . It is a parts field which is require for a invoice.
There a string with this format PartName-QTY-Cost + PartName-QTY-Cost + etc
 I ould love to if possible to split EACH part into 3 different text boxes so in the above example there would be six text boxes. But if this proves to be too difficult I would be happy if i could get each part details on different lines if the same text box. I really need to get this sorted. I have tried using the split function but I can't get it to work correctly .  

Please any help at all is will come .


Thanks
James
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
How many part names can be stored in the one field? This is going to be nasty.

replace({field},"+",chr(13)+chr(10))

Author

Commented:
There can be 0 to 15 parts in one field.

Thanks for the fast reply

Author

Commented:
Do you have any idea how I would be able to loop through the string.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The formula above will allow you to show each part on its own line but in a single text box.
Drop the formula on the report and format it to GROW.

There is no easy way to get each part onto its own separate detail line.  You can get them into 3 text boxes

Try this formula
WhilePrintingRecords;
Global StringVar Array PartsList;
Global StringVar Array PartNameList;
Global StringVar Array QtyList;
Global StringVar Array CostList;
Local StringVar Array PartLine;
'
' Split the string into the part triples
'
PartsList := Split({YourField},'+');

'
'  Split each part triple into three arrays
'
For index := 1 to UBound(PartsList ) do
(
    PartLine := Split(PartsList,'-');
    PartNameList[index] := PartsList[1];
    QtyList[index] := PartsList[2];
    CostList[index] := PartsList[3];
);
''

To display them use
WhilePrintingRecords;
Global StringVar Array PartNameList;
Join(PartNameList,chr(13) + chr(10))

Use a similar formula for each of the 3 arrays

mlmcc

Commented:
if you want differen text fileds and u wanna loop throgh

here is the formula : create formula

@splitingText ===put this formula in detail section

whileprintingrecords;// optional
numbervar i :=1;
numbervar n :=15;
stringvar array str;
redim str[n+1];
str[1] := "welcome to the world of crystal reports"+" ";
numbervar array pos ;
redim pos[n];
 stringvar array result ;
redim result[n];
for i:=1 To n do
(
pos[i] :=  instr( str[i]," ",1);
if pos[i] <> 0 then
(
result[i] := mid(str[i],1,pos[i]);
str[i+1] := ltrim(right(str[i],len(str[i])-pos[i]+1)) ;
)
);
result[4]

====================

replace your filed in below string

str[1] := "welcome to the world of crystal reports"+" ";

assumed your string is separated with " " (one space)

if you have string with separated by comma, then replace with following in above formula

str[1] := "welcome to the world of crystal reports"+",";
pos[i] :=  instr( str[i],",",1);

======================

similarlay you can create as many as text fields as it posses ..replace above formal with
result[1] for 1 text field..
result[2] for 2 text field....


hope this gives you better idea


Author

Commented:
I don't know if the code here will work in visual Studio express 2008  version of crystal reports.
I keep get error like a variable name is expected or just getting values of 1 .

But am not too sure where to put this pieces of code. I right click on the unbound text box and click edit formula. I really am lost here and just need someone who has the time to explain each step in more detail than it already is (even though detail given in the above example it very good it still is not at a low enough level) Thanks for all the help so far PLEASE don't give up on me.

Thanks
James  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There is no version of Crystal in the express version of Visual Studio 2008.

mlmcc

Author

Commented:
Thanks for that info. I am using crystal reports in vb express 2008 is this completely different  ?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is this a standalone version of Crystal or did ut come with VS?

mlmcc

Author

Commented:
I am so sorry just checked I actually have VS 2008 PROFESSIONAL. And yes it came with VS.  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Yes, VS2008 Pro does come with Crystal Reports Basic

In the FIELD EXPLORER
Right click FORMULAS
CLick NEW
Give it a name
Enter one of the formulas
Save it
Put it in the section indicated.

mlmcc

Author

Commented:
I did that and know I get an error when I try and save and close saying A variable name is expected here. It highlights the index word in the code below which I got in one of the previous posts. Can any one see whats wrong with it ? I






Thanks
James

Global StringVar Array PartsList;
Global StringVar Array PartNameList;
Global StringVar Array QtyList;
Global StringVar Array CostList;
Local StringVar Array PartLine;

PartsList := Split({tblMachineHistory.Parts},'+');


For index := 1 to UBound(PartsList ) do
(
    PartLine := Split(PartsList,'-');
    PartNameList[index] := PartsList[1];
    QtyList[index] := PartsList[2];
    CostList[index] := PartsList[3];
); 

Open in new window

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Add another declaration

Local NumberVar index;

All variables must be declared in the formula.  The for loop counter is a variable so it must be declared.  

mlmcc

Author

Commented:
Yeah that solved that problem but how i get

this array must be subscripted . For example Array[i].
and it highlights the Partlist word the the following line

PartLine := Split(PartsList,'-');

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
These lines have an error.   The last 3 PartsList should be PartLine

(
    PartLine := Split(PartsList,'-');
    PartNameList[index] := PartsList[1];
    QtyList[index] := PartsList[2];
    CostList[index] := PartsList[3];
);

Use

(
    PartLine := Split(PartsList,'-');
    PartNameList[index] := PartLine [1];
    QtyList[index] := PartLine [2];
    CostList[index] := PartLine [3];
);


mlmcc

Author

Commented:
Thanks again but I still am getting the same error .

"This array must be subscripted . For example Array[i]."

 PartLine := Split(PartsList,'-');

I really hate to be a pain and really appreciate all the help you are giving me.  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Use

(
    PartLine := Split(PartsList[index],'-');
    PartNameList[index] := PartLine [1];
    QtyList[index] := PartLine [2];
    CostList[index] := PartLine [3];
);

mlmcc

Author

Commented:
We must be getting closer I can save and close the editor but a error appears when I try to run the report
I get   a error a saying

subscript must be between 1 and the size of the array

Any Idea??


james
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you post the formula with the error and show where the error is?

mlmcc

Author

Commented:
This is the code I am using and this is the error (image)


Thanks
James
WhilePrintingRecords;
Global StringVar Array PartsList;
Global StringVar Array PartNameList;
Global StringVar Array QtyList;
Global StringVar Array CostList;
Local StringVar Array PartLine;
Local NumberVar index;
PartsList := Split({tblMachineHistory.Parts},'+');


( 
    PartLine := Split(PartsList[index],'-'); 
    PartNameList[index] := PartLine [1]; 
    QtyList[index] := PartLine [2]; 
    CostList[index] := PartLine [3]; 
); 

Open in new window

Error.jpg
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You are missing the loop


WhilePrintingRecords;
Global StringVar Array PartsList;
Global StringVar Array PartNameList;
Global StringVar Array QtyList;
Global StringVar Array CostList;
Local StringVar Array PartLine;
Local NumberVar index;
PartsList := Split({tblMachineHistory.Parts},'+');
 
For index := 1 to UBound(PartsList ) do
(  
    PartLine := Split(PartsList[index],'-');  
    PartNameList[index] := PartLine [1];  
    QtyList[index] := PartLine [2];  
    CostList[index] := PartLine [3];  
);

mlmcc

Author

Commented:
This is driving me crazy. i am still get the error(in screen shot). any  more ideas. again thanks for all this help
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Here it is.

WhilePrintingRecords;
Global StringVar Array PartsList;
Global StringVar Array PartNameList;
Global StringVar Array QtyList;
Global StringVar Array CostList;
Local StringVar Array PartLine;
Local NumberVar index;

PartsList := Split({tblMachineHistory.Parts},'+');
index := UBound(PartsList);
ReDim  PartNameList[index];
ReDim  QtyList[index];
ReDim  CostList[index];

For index := 1 to UBound(PartsList ) do
(  
    PartLine := Split(PartsList[index],'-');  
    PartNameList[index] := PartLine [1];  
    QtyList[index] := PartLine [2];  
    CostList[index] := PartLine [3];  
);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial