Solved

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

Posted on 2000-04-21
10
176 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

763 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

13 Experts available now in Live!

Get 1:1 Help Now