• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

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
0
kenspencer
Asked:
kenspencer
1 Solution
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
ebolekCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now