Solved

Populate a multi-line formula field dynamically

Posted on 2004-07-30
14
939 Views
Last Modified: 2008-02-01
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
Comment
Question by:kenspencer
14 Comments
 
LVL 3

Author Comment

by:kenspencer
ID: 11680288
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
 
LVL 10

Expert Comment

by:ebolek
ID: 11680405
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
 
LVL 3

Author Comment

by:kenspencer
ID: 11680509
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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 10

Expert Comment

by:ebolek
ID: 11680538
well if you are using vb6

then write it like this

text = "Select Select {qryQtrRpt_A999_Report.Type}" +
   " " + "case "  + """"+ " " + ": 880" +
.......
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11680702
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
 
LVL 10

Expert Comment

by:ebolek
ID: 11680876
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
 
LVL 10

Accepted Solution

by:
ebolek earned 250 total points
ID: 11680928
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 11681364
instead of vblf try using
chr(13)

mlmcc
0
 
LVL 3

Author Comment

by:kenspencer
ID: 11693694
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
 
LVL 13

Expert Comment

by:EwaldL
ID: 11730879
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
 
LVL 3

Author Comment

by:kenspencer
ID: 11752884
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
 
LVL 10

Expert Comment

by:ebolek
ID: 11752917
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
 
LVL 3

Author Comment

by:kenspencer
ID: 11753578
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
 
LVL 10

Expert Comment

by:ebolek
ID: 11753620
Glad to help
Emre
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question