Solved

Compare variable's value in crystal report......

Posted on 2000-04-21
10
177 Views
Last Modified: 2011-09-20
I tried to wrote same formula in 2 different ways but unfortunately it didn't run.
It gives me below mentioned error when i try to preview my report.

1)Error: Numeric Overflow.

StringVar X;
If {table_site.x_site_group} = "Bldg" then
    X := chr({table_site.objid})
Else If {table_site.x_site_group} = "Tenant" then
    If chr({table_site.child_site2site}) = X then
        {table_site.name}
    Else
        ""
Else
    ""

2)Error:The String is non-numeric.

StringVar X;
If {table_site.x_site_group} = "Bldg" then
    X := chr({table_site.objid})
Else If {table_site.x_site_group} = "Tenant" then
    If {table_site.child_site2site} = ToNumber(X) then
        {table_site.name}
    Else
        ""
Else
    ""

Let me know as soon as you can.


Thanks for your time in advance.

Thanks,
bhavesh55
0
Comment
Question by:buddyp
  • 5
  • 4
10 Comments
 
LVL 1

Accepted Solution

by:
prakashbitra earned 100 total points
ID: 2739388
hi buddyp,

 initially when u create a variable it has some garbage value in it, so we have to assign a value to that variable X.

for ur code, first if statement will work good but when it reaches to else statement u r directly comparing the database field to a garbage value..



bye
happy working
0
 
LVL 18

Expert Comment

by:mdougan
ID: 2740768
Hello,

I posted what I believe is the correct answer for you in one of the other two versions of this same question, but I'll post it here as well.  I think the problem is that you have used the chr function which is supposed to be used to convert one ascii value to one character, to try and convert a string.  To convert a number to a longer string, use the ToText function instead of the chr function.
0
 

Author Comment

by:buddyp
ID: 2742282
prakashbitra & mdougan

I wrote below mention code:

StringVar X;
If {table_site.x_site_group} = "Bldg" then
    X := ToText({table_site.objid},0)
Else If {table_site.x_site_group} = "Tenant" then
    If ToText({table_site.child_site2site},0) = X then
        {table_site.name}
    Else
        "" 
Else
    "" 

It shows me value of "{table_site.objid}".Actually in "Else if" part i wrote "{table_site.name}".Means if condition is satisfied than it should display that value.Instead of that it displays me value of "objid".

Let me know how do i resolve this problem.Or whats wrong with it ?

Thanks for your time in advance.

I am eagerly waiting for your reply.

Thanks,
buddyp
0
 
LVL 18

Expert Comment

by:mdougan
ID: 2742728
What is confusing in your logic is that in one condition, you are assigning a value to X, but in the other conditions, you are telling Crystal to return a value to display.  If you are seeing the objid, then it can only mean that the first If conditions is being satisfied.  

If you never want to see the value of objid, then you should probably change your formula to something like this:

StringVar X;
If {table_site.x_site_group} = "Bldg" then
    X := ToText({table_site.objid},0);

If {table_site.x_site_group} = "Tenant" then
    If ToText({table_site.child_site2site},0) = X then
        {table_site.name}
    Else
        "" 
Else
    "" 
0
 

Author Comment

by:buddyp
ID: 2743510
mdougan
I wrote your code but it doesn't show me {table_site.name} which i wrote in second condition.

My database is like:

objid     child_site2site x_site_group
 1            Null          Bldg
                1           Tenant
                1           Tenant

As i mention above "objid" of "Bldg" record is same as "child_site2site" of "Tenant" records.so thats why i try to store that objid and compare it with other records and try to wrote "Building name" of that "tenant".

There are more than one "Tenant" lying under the same building.

So Let me know as soon as you can.

I am eagerly waiting for your reply.

Thanks,
buddyp

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:mdougan
ID: 2745764
I think I see the problem.  Tell me what you want Crystal to do.  It looks like you want Crystal to read the BLDG record, and set X to it's objid of 1.  And, you want to display "" for that record.  Then, when you read the next record, because it's group is Tennant, you don't want to change X, but if it's site2site matches X then you want to print the bldgs name, otherwise you want to print ""

If you want X to maintain it's value from one record to the next, then you have to add the statement:

WhilePrintingRecords;
StringVar X;

Your other code...

But, there is probably a better way to do what you're trying to do.  In general, with a Crystal report, your're trying to create "denormalized" rows. Your database rows look kind of odd in that they're displaying two types of rows in the same result set.
0
 

Author Comment

by:buddyp
ID: 2749462
mdougan
You understood my question properly.

Let me clarify in detail.

When value of Tenant's child_site2site = Bldg's objid field than i want to print name of the "Bldg" and "Tenant".

My Databse:
objid Name child_site2site x_site_group
 1     A       Null          Bldg
       A1         1           Tenant
       A2         1           Tenant

I mean it prints :
A    <- Bldg's name for below Tenant
A1   <- Tenant's name for above bldg

Let me know how can i solve this.I mean what should i wrote to do this ?
Thanks,
buddyp

0
 
LVL 18

Expert Comment

by:mdougan
ID: 2749878
Well, the main problem is that you have two different objects in the same table.  What you should really have is the following structure

Buildings
bldgid bldgName
 1      A
 2      B

Tenants
tenantid   bldgid   tenantName
 10           1      A1      
 11           1      A2      
 12           2      Q1      
 13           2      Q2      

Then, your sql would look like

Select bldgName, tenantName
from Buildings, Tenants
where Buildings.bldgid = Tenants.bldgid
order by bldgname, tenantName

In your Crystal Report, you'd group by bldgName, and in the Group Header, you'd display the Building Name, and in the detail, you'd have the tenants names.

You might be able to do a similar thing even given your current table structure, but it wouldn't be as good.  Tell me if you can change your table structures at all.
0
 

Author Comment

by:buddyp
ID: 2752221
mdougan
I can't change the database as i show you above.

But eventhen i want to Put "Building Name" and "Tenant Name" in my report as 2 separate column.Let me know how do i do that ?

Building Name     Tenant Name
    A                  A1
    A                  A2

Is that any how i can do this by using formula or sql expression.

I am eagerly waiting for your reply.

Thanks,
buddyp
0
 
LVL 18

Expert Comment

by:mdougan
ID: 2753829
OK, we'll see what we can do, but as you will see, it's always harder to do something the wrong way, than it is to do it the right way.

If you look at the Report menu, you'll see Show SQL Statement.  You probably have something that looks like:

Select
   objid
   Name
   child_site2site
   x_site_group
From Table_Site

Change this to:

Select
   objid
   Name
   "1" as child_site2site
   x_site_group
From Table_Site
Where child_site2site is null
Union
Select
   child_site2site as objid
   Name
   "2" as child_site2site
   x_site_group
From Table_Site
Where child_site2site is not null

We're kind of shifting the columns around, and don't worry about what we're doing to the column child_site2site, we'll need this later.

Save the SQL Statement, and then go back in and edit it again, just to verify that your changes are still there.  Crystal makes it hard to change the SQL Statement sometimes.

Then, you want to go to the report menu (I think) and select the Group Expert.  Tell it that you want to Group on the objid column, and then sort on the child_site2site.

The easy way to do the output is that in the Group Header, you'd Display the Name Field from the database (which will have the building name), and in the detail, you'd display the name field (which will have the tenant name)

But, if you really want them on the same line, then you're going to have to create a formula field.  Call it something like BldName (which Crystal will make @BldName).  The formual will just be:

WhilePrintingRecords;
StringVar xName;

xName := {Table_site.Name}

Put this formula field in the Group header area, right click on it, and make sure that the checkbox called "Surpress" is checked, this will keep it from appearing on your report.

Now, create another Formula field.  Call this DisplayName (@DisplayName) and it's formula is:

WhilePrintingRecords;
StringVar xName;

xName

Put this formula on your detail line, under the header for the Building Name.  Put the plain database Name field unde the header for the Tenant name.

Now, the last piece, look to the far left, (your left) right click there and edit the Section Format.  Make sure that the Detail Section is highlighted, and click on the button next to the Surpress checkbox that looks like X>.  Enter the following formula:

{Table_Site.child_site2site} = 1

What this formual is really saying is that if the child_site2site = 1 (which it will if it's a bldg) then the statement is True, in which case Crystal will Surpress the detail line (so you don't display the detail for rows that are buildings).

There is another way to do this from the start, that is a little easier, but I'll only go into that if you can't get this to work.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

930 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now