Pulling Pieces of Data from a "&" delimeted field

[QueTech]

Hi All,

I am pulling data from a Cache database using Crystal 10 on a Window Xp workstation.

I data in a text field that I need to brake out into seperate variable to be displayed and manipulated. Here is an example of the data ".60&7.83&1.45&3". This is the formula I want to use to pull the first piece of data.

if isnull({provider_data_demographic.license_number}) then
0
else
if isnumeric(Split ({provider_data_demographic.license_number}, '&')[1]) then
tonumber(Split ({provider_data_demographic.license_number}, '&')[1])
else
0
and I get .60 then I use the formula below to pull the second peice.

if isnull({provider_data_demographic.license_number}) then
0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 1 then
0
else
if isnumeric(Split ({provider_data_demographic.license_number}, '&')[2]) then
tonumber(Split ({provider_data_demographic.license_number}, '&')[2])
else
0
I get "7.83"

but when I use the formula:

if isnull({provider_data_demographic.license_number}) then
0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 1
then 0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 2 then
0
else
if isnumeric(Split ({provider_data_demographic.license_number}, '&')[3]) then
tonumber(Split ({provider_data_demographic.license_number}, '&')[3])
else
0

I get "1"

And if I use the following formula for the forth peice I get "0".

if isnull({provider_data_demographic.license_number}) then
0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 1 then
0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 2 then
0
else
if ubound(Split ({provider_data_demographic.license_number}, '&')) <= 3 then
0
else
if isnumeric(Split ({provider_data_demographic.license_number}, '&')[4]) then
tonumber(Split ({provider_data_demographic.license_number}, '&')[4])
else
0

I know I,m missing something. There must be a better way.

 

[Madawc]

I'd suggest you split the single gigantic test into a set of separate formula fields. Display each on a test version of the report. See which one isn't working the way you expect.

If you use boolians - tests like ubound(Split ({provider_data_demographic.license_number}, '&')) <= 3, with no IF statement - then you'll get a result of 'true' or false'. You can then refer to them by name in another formula field etc, as @Test3=true or just @Test3

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Your formula worked fine when I tested it in XI, although it's unnecessarily complex. You could just be using:

if ubound(Split ({provider_data_demographic.license_number}, '&')) >= 4 and
isnumeric(Split ({provider_data_demographic.license_number}, '&')[4]) then
tonumber(Split ({provider_data_demographic.license_number}, '&')[4])

Anyway, not sure why it isn't working for you. Is the string you showed exactly what is in the database or might there be some spaces in it?

-LB

 

[QueTech]

Thanks I'll try both suggestions and check the data for spaces. I'll will p[ost my results.

 

[QueTech]

I have found the problem with my formula or more to point the problem with the data. The field I am pulling data from allows me to enter more data than the field is design to hold. The field is set to hold 10 charactors.

Thanks