×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

receiving error message on split formula

receiving error message on split formula

receiving error message on split formula

(OP)
Hey guys I have the following formula:
//{@one}
if ubound(Split({@Tax County Footer}, ","))>0 then(split({@Tax County Footer}, ",")[1]) else "0"
I believe I am having an issue because in the split I have some records that are null if this is the case it needs to skip @one and go to the @two group to process. I had the same issue with my group 2 originally but I was able to fix it. I have tried several different formulas on this one and no luck.
Please Help
Thanks

RE: receiving error message on split formula

What is the error message?

RE: receiving error message on split formula

(OP)
Array's dimension must be an interger between 1 and 1000

RE: receiving error message on split formula

If you are trying to break a string into two pieces, you probably don’t need to use split() or arrays. A first step is to show the contents of {@Tax County Footer} and of any nested formulas. Secondly, you should explain what you are trying to do with your {@one} and {@two} formulas.

-LB

RE: receiving error message on split formula

(OP)
So I am pulling from a field that can have up to two values. I had to use a stringvar formula to show both amounts. I then used the split to break these two values into separate fields one is state tax one is county tax. I had to pull the items I needed from the details group into the group footer so I did not see the multiple lines for each invoice that showed the tax. The county footer is the county portion of the sales tax string.
whileprintingrecords;
stringvar county;
Mid(county,2);
I have the same type of setup for the state. I do not use crystal very often so I am trying to figure out as I go. I believe I got the state formula fixed I was having the same error with it I used the following formula for it:
//{@two}
If ubound (split({@Group Footer}, ","))>1 then Split({@Group Footer}, ",")[2];

Please let me know what I am doing wrong.
thanks

RE: receiving error message on split formula

You need to show content of all formulas. Can’t follow this so far. It might help if you show a mock up of sample data and what you would like your formula to generate as a a result of that.

-LB

RE: receiving error message on split formula

(OP)
Below is a sample of the output without the split function:

Vendor Paid Date Invoice# Invoice Amt SalesTax Amount County
BB&T 11/18/18 101118 106.45 2.13 Union
BB&T 11/18/18 101118 106.45 5.06 NC State

I grouped the report by vendor number so I could see all of the tax entries for each invoice. The Sales Tax is coming from the invoice distribution table field is named amount. I used the split to combine both amounts on one line. I pulled the vendor name, paid date, invoice number, and invoice amount to the group footer section and I suppressed the detail section.
I used the following formulas to split the tax to show on the same line (I tried several this is the only one I could get to work)
In the Group Header (Header): whileprintingrecords;stringvar names := "";
In the Details (Details): whileprintingrecords;stringvar names;names := names & ", " & {tblAPInvDistributions.Amount};
In the Footer (Group Footer): whileprintingrecords;stringvar names;Mid(names,2);
This worked to show both amounts separated by a comma: 2.13,5.06
I then created the following formula to show just the first amount(county footer):
//{@one}if NOT IsNull ({@Group Footer})then if isnumeric (Split({@Group Footer}, ",")[1]) then ToNumber((Split({@Group Footer}, ","))[1])else 0

I did the same breakdown for the state calculation


RE: receiving error message on split formula

You should do the null check on the amount field, not on the formula as a whole, since I’ guessing the name field is always non-null. You might want to set it up so a null will appear as a 0, as in:

Names := names &”,”&if isnull({table.amt}) then 0 else {table.amt}

Rewrite this within your names formula (don’t use my quote marks as they will cause the formula to fail).

It concerns me that you are capturing results in the group footer—are you sure there will always be only two rows per group?

Another approach would be (if there are only two rows per invoice #) to create conditional formulas like this for the detail section:

If {table.county}=“State” then {table.amt}

If {table.county}<> “State” then table.amt}

Then insert maximums on the formulas at the group level.

-LB

RE: receiving error message on split formula

(OP)
I put the isnull formula in name still got the error message regarding the array. I tried the state formula originally before the split. It would not work always pulls in a zero amount for the state.

RE: receiving error message on split formula

Are the tax amounts ever in the 1000's? This would cause the formula to fail. Try changing the "," to a "^" in both the names formula and the split formulas, and then report back, please.

Also, please post your state formula, if only it is failing.

-LB

RE: receiving error message on split formula

(OP)
Will do... It's my county that is failing not the state with the current formulas (the array formula). The state only failed when I try to do the state formula within the details with the conditional formula

RE: receiving error message on split formula

Also, I think your formulas should check for number of members in the array:

//{@one:
if ubound(split({@group footer},","))>=1 then
ToNumber(split({@group footer}, ",")[1])else 0

//{@two}:
if ubound(split({@group footer},","))>1 then
tonumber((Split({@Group Footer},",")[2]))else 0

-LB

RE: receiving error message on split formula

(OP)
I had some transactions that were over 1,000 when I changed all of the formulas to the ^ I am not receiving the message anymore. Thanks so much for your help. Do I still need to check the number of members?

RE: receiving error message on split formula

It can’t hurt, and adds another check.

-LB

RE: receiving error message on split formula

(OP)
thanks again for all of your help

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close