Separating a Dynamic Database field 1

[djmuli]

Ok I have a query that returns a few fields, one of the fields has multiple amounts of data in the one field.

E.g. 1,22223,090009978,9767666

How do I split that field via the commas so i have

1 22223 090009978 9767666

The data will change everytime i refresh the report, is this possible?

thanks

 

[lbass]

You can use formulas like this:

//{@1st element}:
if ubound(split({table.field},",") >= 1 then
split({table.field},",")[1]

//{@2nd element}:
if ubound(split({table.field},",") >= 2 then
split({table.field},",")[2]

Add as many formulas as there might be elements in the field.

-LB

 

[djmuli]

ok that looks good

but when i try to save the formula it doesn't seem to like the "then" part?

 

[lbass]

Sorry, forgot some parens:

//{@1st element}:
if ubound(split({table.field},",")) >= 1 then
split({table.field},",")[1]

//{@2nd element}:
if ubound(split({table.field},",")) >= 2 then
split({table.field},",")[2]

-LB

 

[djmuli]

thanks so much for your help, really appreciate it.

I'm still getting errors though?

I'm using it as a formatting formula, is that correct?

 

[lbass]

No, these should be created in the formula expert and then dragged onto the report to use as fields.

-LB

 

[djmuli]

ok still not working

Sorry if i'm missing something totally obvious, but I'm using the formula workshop and it is still bringing up an error.

I can't even use the simple split fucntion without it failing.

 

[synapsevampire]

If you post what you tried, where, and any error messages, saying you recevedi aqn error doesn't help much....

Chatting about it is OK, but first post meaningful information. It could be a datatype issue, the version of Crystal, how you tried to do it, etc.

Note that the 1st question a tech support person asks is what version and edition of the software you're using, kindly provide the same here.

LB just pointed otu an issue for another user that may be yours, what LB supplied is 2 different formulas, not one.

Anyway, if it doesn't work, post EXACTLY what you tried, where and the error rather than you tried it, your field names are different and you might have made a syntax error (as LB had, I do it often).

-k

 

[djmuli]

Business Object: Crystal Reports 11

I went into Report > Formula Workshop

Then went New > Formula and created it there.

When I check it, it just comes up as error, won't tell me what the error is.

I save it anyway, put it in the report, and then obvisouly it won't work and opens it up for me to edit it, still not identifying what the error is.

here is the exact formula.

if ubound(split({Command.Data},",")) >= 1 then
split({Command.Data},",")[1]

 

[djmuli]

ok it works now, with one formula per row of data

the very weird thing is, i didn't change the formula

well not that i can see

 

[synapsevampire]

A sign of pure genius, you fixed it without changing it!!!

Glad that it's functioning now.

-k

 

[pblazeppd]

I'm trying to split an address field. The field contains data as follows:

Current Want to display
Address
871 Main St 800 Main St
1010 Kline St #214 1000 Kline St
Kline St/Main St Kline St/Main St

The field is a 38chr string. The numeric portion may be 3-6 characters, separated by a space, then the street name, space, street type, space and possibly an apartment (ie.#3). The field can also be an intersection. I would also like to round the numeric portion of the separated field. Then display the fields. The main purpose is not to display the actual address, just the block.

Will the following formula from a previous post get things started?

//{@1st element}:
if ubound(split({table.field},",")) >= 1 then
split({table.field},",")[1]

//{@2nd element}:
if ubound(split({table.field},",")) >= 2 then
split({table.field},",")[2]

All help is appreciated. Thanks in advance...

 

[lbass]

You should have started a new thread, as this is a different topic. Try:

stringvar addr :=
if isnumeric(split({table.address}," ")[1]) and
instr({table.address},"/") = 0 then
(
if val(split({table.address}," ")[1]) < 100 then
totext(int(val(split({table.address}," ")[1])/100)*100,"00") else
totext(int(val(split({table.address}," ")[1])/100)*100,0,"")
) +" "+ mid({table.address}, instr({table.address}," ")+1) else
{table.address};
if instr(addr,"#") > 0 then
trim(left(addr,instr(addr,"#")-1)) else
addr

-LB

 

[pblazeppd]

That worked fine.

Thanks again

PLB