Report Selection formula Query 2

[samson123]

I am using CR XI , Access 2000

I have a master table which stores all the codes and their descriptions.

MCodes (ID is the PK)
--------
ID Code_type Desc
1 Status Active
2 Status Inactive
3 ContactType Employee
4 ContactType Vendor
5 ContactType Temp

I need to display the above Description in the report.

For exmple

Name Status Type
John Smith Active Employee
John Doe Inactive Employee
Peter King Active Temp

I have stored in CONTACT Table
-------------------------------
Name StatusID, TypeID
John Smith 1 3
John Doe 2 3
Peter King 1 4

In my report selection formula I have

CONTACT.StatusID = MCODES.ID or
CONTACT.TypeID = MCODES.ID

I have following formula for Status and Type

@Status
if code_type = 'Status' then
Mcodes.Desc

@Type
if code_type = 'Type' then
Mcodes.Desc

But I just see only the names in the report

Name Status Type
John Smith
John Doe
Peter King

What am I doing wrong ?

 

[lbass]

In the linking expert (database->visual linking expert), add the codes table twice. Link {Contacts.StatusID} to {Codes.ID} and link {Contacts.TypeID} to {Codes_1.ID}. Then you should be able to just place each description field on the report.

-LB

 

[dgillz]

You should have squiggly brackets on both sides of the field names. If you use crystal to write the formula, rather than writing it manually, this will happen automatically. Here's an example:


@Type
if {code_type} = 'Type' then
{Mcodes.Desc}

Alternatively, the field could be null. I f this is the case then try this formula:

If Isnull({code_type}) then "None" else {Mcodes.Desc}

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[samson123]

Thanks LB and dgillz for a quick repsonse. I was not aware you could add the same table twice. I am guessing I can add the same table any number of times using the same logic ? Because I have a number of codes in the MCOdes table.

Dgillz, thanks for the ISnull approach.

I have given both of you a star..

 

[lbass]

Yes, you can add the codes table multiple times. The first time a "_1" will be appended to the table name. After that, it might append a "_1" again, so you will need to change it to "_2" or to some text that helps you keep the codes straight, e.g., CODES_status, or CODES_type. If you use lower case, you can distinguish formal table names from your alias extensions.

-LB