Solved

Populate a multi-line formula field dynamically

Posted on 2004-07-30
14
943 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 101

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

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.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

690 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