Formula to Group on 2

[blueboyz]

Using Peachtree Premium 2010, Crystal Reports 2008 on pervasive SQL database engine.

I am trying to create a group based on account numbers.
I created the formula:

If (InStr({Chart.AccountID} in ["5000" to "5900"])>0) then 1
else if (InStr({Chart.AccountID} in ["6000" to "6399"])>0) THEN 2
ELSE IF (InStr({Chart.AccountID} IN ["6400" TO "6900"])>0) THEN 3
ELSE IF (InStr({Chart.AccountID} IN ["7000" TO "7005"])>0) THEN 4
ELSE IF (InStr({Chart.AccountID} IN ["7025" TO "7960"])>0) THEN 5
ELSE IF (InStr({Chart.AccountID} IN ["8025" TO "8650"])>0) THEN 6

I receive an error when I try to save the formula: not enough arguments have been given to this function.

How can I fix this formula?

 

[fisheromacse]

InStr, looks for string values, not numeric values, as such a range (5000 to 5900) will not work.

The InStr function's syntax is generally like this:
InStr({Table.Field},"TextToFind")

 

[blueboyz]

I think I figured it out:

If (InStr ({Chart.AccountID},"5000")>0) then 1
else if (InStr ({Chart.AccountID},"5050")>0) then 1
else if (InStr ({Chart.AccountID},"5060")>0) then 1
else if (InStr ({Chart.AccountID},"5070")>0) then 1
else if (InStr ({Chart.AccountID},"5100")>0) then 1
else if (InStr ({Chart.AccountID},"5200")>0) then 1
else if (InStr ({Chart.AccountID},"5325")>0) then 1
else if (InStr ({Chart.AccountID},"5400")>0) then 1
else if (InStr ({Chart.AccountID},"5550")>0) then 1
else if (InStr ({Chart.AccountID},"5625")>0) then 1
else if (InStr ({Chart.AccountID},"5650")>0) then 1
else if (InStr ({Chart.AccountID},"5725")>0) then 1
else if (InStr ({Chart.AccountID},"5825")>0) then 1
else if (InStr ({Chart.AccountID},"5900")>0) then 1
else if (InStr ({Chart.AccountID},"6000")>0) then 2
else if (InStr ({Chart.AccountID},"6005")>0) then 2
else if (InStr ({Chart.AccountID},"6010")>0) then 2
else if (InStr ({Chart.AccountID},"6050")>0) then 2
else if (InStr ({Chart.AccountID},"6100")>0) then 2
else if (InStr ({Chart.AccountID},"6125")>0) then 2
else if (InStr ({Chart.AccountID},"6150")>0) then 2
else if (InStr ({Chart.AccountID},"6200")>0) then 2
else if (InStr ({Chart.AccountID},"6275")>0) then 2
else if (InStr ({Chart.AccountID},"6300")>0) then 2
else if (InStr ({Chart.AccountID},"6399")>0) then 2
else if (InStr ({Chart.AccountID},"6400")>0) then 2
else if (InStr ({Chart.AccountID},"6450")>0) then 3
else if (InStr ({Chart.AccountID},"6460")>0) then 3
else if (InStr ({Chart.AccountID},"6470")>0) then 3
else if (InStr ({Chart.AccountID},"6480")>0) then 3
else if (InStr ({Chart.AccountID},"6500")>0) then 3
else if (InStr ({Chart.AccountID},"6525")>0) then 3
else if (InStr ({Chart.AccountID},"6550")>0) then 3
else if (InStr ({Chart.AccountID},"6565")>0) then 3
else if (InStr ({Chart.AccountID},"6575")>0) then 3
else if (InStr ({Chart.AccountID},"6600")>0) then 3
else if (InStr ({Chart.AccountID},"6625")>0) then 3
else if (InStr ({Chart.AccountID},"6650")>0) then 3
else if (InStr ({Chart.AccountID},"6675")>0) then 3
else if (InStr ({Chart.AccountID},"6700")>0) then 3
else if (InStr ({Chart.AccountID},"6725")>0) then 3
else if (InStr ({Chart.AccountID},"6750")>0) then 3
else if (InStr ({Chart.AccountID},"6775")>0) then 3
else if (InStr ({Chart.AccountID},"6800")>0) then 3
else if (InStr ({Chart.AccountID},"6850")>0) then 3
else if (InStr ({Chart.AccountID},"6900")>0) then 3
else if (InStr ({Chart.AccountID},"7000")>0) then 4
else if (InStr ({Chart.AccountID},"7002")>0) then 4
else if (InStr ({Chart.AccountID},"7003")>0) then 4
else if (InStr ({Chart.AccountID},"7005")>0) then 4
else if (InStr ({Chart.AccountID},"7025")>0) then 5
else if (InStr ({Chart.AccountID},"7030")>0) then 5
else if (InStr ({Chart.AccountID},"7050")>0) then 5
else if (InStr ({Chart.AccountID},"7100")>0) then 5
else if (InStr ({Chart.AccountID},"7125")>0) then 5
else if (InStr ({Chart.AccountID},"7650")>0) then 5
else if (InStr ({Chart.AccountID},"7775")>0) then 5
else if (InStr ({Chart.AccountID},"7950")>0) then 5
else if (InStr ({Chart.AccountID},"7960")>0) then 5
else if (InStr ({Chart.AccountID},"8025")>0) then 6
else if (InStr ({Chart.AccountID},"8050")>0) then 6
else if (InStr ({Chart.AccountID},"8150")>0) then 6
else if (InStr ({Chart.AccountID},"8200")>0) then 6
else if (InStr ({Chart.AccountID},"8250")>0) then 6
else if (InStr ({Chart.AccountID},"8300")>0) then 6
else if (InStr ({Chart.AccountID},"8350")>0) then 6
else if (InStr ({Chart.AccountID},"8352")>0) then 6
else if (InStr ({Chart.AccountID},"8360")>0) then 6
else if (InStr ({Chart.AccountID},"8400")>0) then 6
else if (InStr ({Chart.AccountID},"8450")>0) then 6
else if (InStr ({Chart.AccountID},"8460")>0) then 6
else if (InStr ({Chart.AccountID},"8500")>0) then 6
else if (InStr ({Chart.AccountID},"8510")>0) then 6
else if (InStr ({Chart.AccountID},"8520")>0) then 6
else if (InStr ({Chart.AccountID},"8525")>0) then 6
else if (InStr ({Chart.AccountID},"8530")>0) then 6
else if (InStr ({Chart.AccountID},"8600")>0) then 6
else if (InStr ({Chart.AccountID},"8650")>0) then 6

This lets me group on this formula and sorts the account numbers in a range in account # order.

Thanks.

 

[briangriffin]

Ugh...as fisherromacse points out, the InStr function doesn't seem to be needed here. Try something like this:

If {ChartAccountID} in ["5000" to "5900"] then 1
else if {ChartAccountID} in ["6000" to "6399"] THEN 2
ELSE IF {ChartAccountID} IN ["6400" TO "6900"] THEN 3
ELSE IF {ChartAccountID} IN ["7000" TO "7005"] THEN 4
ELSE IF {ChartAccountID} IN ["7025" TO "7960"] THEN 5
ELSE IF {ChartAccountID} IN ["8025" TO "8650"] THEN 6

Your long version works, which is good, but has to be modified any time a new account is added, which is bad. The range approach still works as long as new accounts are in the range, which they usually are.

 

[blueboyz]

Thank you, the range is a much bettr approach.