Populate a multi-line formula field dynamically

Hi,
I am trying to populate a CR formula field (from VB6) with a 'Select' statement followed by several 'Case' statements.  I have tried separating the 'Case' statements with spaces, commas, carriage-returns, line-feeds, and combination of carriage-return/line-feed.  I always get a formula error.  Here is a sample of what I am trying to do:

Select {qryQtrRpt_A999_Report.Type}
   case "  ": 880
   case "10": 17
   case "12": 5
   .
   .
   .
   default: 0;

I am dynamically determining both the 'case' selection item and its number.  Trying to send the entire 'Select' statement as a string for the formula results in an error.    Any ideas?

Ken
LVL 3
kenspencerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ebolekConnect With a Mentor Commented:
Also this line seems wrong .
g_crwRpt.FormulaFields.Item(x).Text = "'" & g_sCrystalFormulae(x) & "'"
g_sCrystalFormulae(x) is already a string you dont need t ouse "" 

g_crwRpt.FormulaFields.Item(x).Text = g_sCrystalFormulae(x)

Regards
Emre


0
 
kenspencerAuthor Commented:
Sorry -- slightly mis-stated.  The numbers (after the : of the case statement) are being returned as strings (since I thought it would be simpler).  Same problem, though.
0
 
ebolekCommented:
your formula select statement has no mistake

Select {Customer.Fax}[1 To 3]
   Case "604", "250" :
      "BC"
   Case "206", "509", "360" :
      "WA"
   Default :
      "";

If the type of the {qryQtrRpt_A999_Report.Type} is string then it should fine . if it is not covert it to string
write like this
Select cstr({qryQtrRpt_A999_Report.Type})

Regards
Emre
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kenspencerAuthor Commented:
Emre,
I am trying to send the entire Select statement as a single string (from VB6 to Crystal), so in your example, what character(s) should I concatenate in the string after the : (colon)?

Ken
0
 
ebolekCommented:
well if you are using vb6

then write it like this

text = "Select Select {qryQtrRpt_A999_Report.Type}" +
   " " + "case "  + """"+ " " + ": 880" +
.......
0
 
kenspencerAuthor Commented:
Here's what I am doing:

       Q = Chr$(34)
        x = 0
        tmp = ""
        With rs         'ADODB recordset
            .Open qry, g_cnReports, adOpenStatic, adLockReadOnly
            Do While Not .EOF
               '  In rs, !Type is a string, !TypeCnt is an integer
                tmp = tmp & vbLf & "   case " & Q & !Type & Q & ": " & Q & CStr(!TypeCnt) & Q
                x = x + !TypeCnt
                .MoveNext
                DoEvents
            Loop
            .Close
        End With

        tmp = "Select {qryQtrRpt_A999_Report.Type}" & vbLf & "   case " & Q & "  " & Q & ": " & Q & CStr(x) & Q & tmp
        tmp = tmp & vbLf & "   default  : " & Q & "0" & Q & ";"
        g_sCrystalFormulae(6) = tmp

       Then later I send the formula to Crystal as:
            g_crwRpt.FormulaFields.Item(x).Text = "'" & g_sCrystalFormulae(x) & "'"     <-- those are single quotes surrounded by double-quotes

What I am having trouble with is separating the 'case' statements.  In my example I am using a combination of a line-feed and 3 spaces, but Crystal seems to object to that.  If I use vbCr or vbCrLf, it seems to read the carriage-return as the end of the statement.  Unless I have made a typo somewhere, just spaces or commas also fail.

Ken
0
 
ebolekCommented:
you are sure item 0 is the formula that you need right.Wht dont you refer to the formula with the name of it in the collection.

You are incrementing x in the while loop are you sure that you have that item in the collection

0
 
mlmccCommented:
instead of vblf try using
chr(13)

mlmcc
0
 
kenspencerAuthor Commented:
Emre,
Sorry I was not clear in my last post.  The last line, where I set g_crwRpt.FormulaFields.Item(x).Text to a value is in a different subroutine, and x begins with 1, not 0.  Also, when I take out the single quotes surrounding the string, Crystal gives me an error; but it does work with the string surrounded by the single quotes.

mimcc,
I have tried vbCrLf (which = Chr(13) & Chr(10)), vbCr (which is Chr(13)), vbLf (which is Chr(10)), commas, spaces and combinations thereof, but I have been unsuccessful in getting the formula to dynamically accept multiple 'case' statements with some form of new line indicator.  I would settle for 1 long line if I knew what I need to use to separate the 'case' statements.

Ken

0
 
EwaldLCommented:
the following did the trick for me

  crReport.FormulaFields.Item(1).Text = "Select {Customer.Customer Name}" _
   + "case '  ': 880" _
   + "case 'Poser Cycles': 2222" _
   + "default: 0;"
0
 
kenspencerAuthor Commented:
Emre,
(I have been on vacation for a week)
It appears that you have provided a multi-line VB statement; what I need is a multi-line formula text as the value of crReport.FormulaFields.Item(x).Text.  From your example it appears that the formula would end up being:

Select {Customer.Customer Name}case ' ': 880case 'Poser Cycles': 2222default: 0;

Did Crystal actually interpret that correctly (in other words, have I somehow made a mountain out of a mole hill)?

Ken
0
 
ebolekCommented:
Yes it would do the trick for you. The formula text will be like this then use the code that i posted to set the formla text dynamically

Select {Customer.Customer Name} case ' ': 880 case 'Poser Cycles': 2222 default: 0;

Regards
Emre

P.S. I was on vacation last week too. :)
0
 
kenspencerAuthor Commented:
Woops ... appears my mind was still on vacation.  Sorry EwaldL -- thought you were Emre.

Anyway, I found the problem.  Until now, all of my Crystal formula items were treated as text in my VB program and surrounded with a single quote when sent to Crystal (works fine).  However, since this is actually a *real* formula and not just a simple string, then removing the single quotes surrounding the entire statement was indeed the answer.  So, it seems that Emre's post from 7/30/2004 03:02PM was the right answer, although my feeble mind got confused.  :)

Therefore, the points will be going to Emre.  Thanks to everyone for their help.

Ken
0
 
ebolekCommented:
Glad to help
Emre
0
All Courses

From novice to tech pro — start learning today.