Mutiple Databases

[RachelK]

Hi,

I have a database that I am writing a crystal report with there is a field in the database which holds the engineers initials but I would like the engineers name
so I have created a seperate ms access database with one table with Engineers initials and Engineers Full name I have linked Engineers Initals in the main database I have written this formula

If Left({@Engineer},3)
Like
Left({ENGINEERS_NAMES.ENGINEER_INITIALS},3)
Then
{ENGINEERS_NAMES.ENGINEERS_FULL_NAME}
Else
{@Engineer}

However I cannot get the new Database I have brought in to bring back any data. If I look in the visual linking expert and click on browse field data I can see there is data there,

 

[Naith]

Create a formula that says simply:

Left({@Engineer},3)
=
Left({ENGINEERS_NAMES.ENGINEER_INITIALS},3)

If the results come up 'False', then that's why you're not getting any data. Otherwise, post what is in the {@Engineer} formula.

Also, confirm your linking strategy between the two database objects.

Naith

 

[RachelK]

Naith,

This is the engineer formula I have tried what you suggested and I am getting False.

If ISNull({JOBXREF.ENGINEER})
or
{JOBXREF.ENGINEER} = "" or
{JOBXREF.ENGINEER} = " " or
{JOBXREF.ENGINEER} = " "
Then
"N/A"

Else {JOBXREF.ENGINEER}

 

[RachelK]

I have also found that if I do not connect the second database the main db via the visual linking expert it will bring false however if I link them together it will bring back nothing.

 

[Naith]

Change your {@Engineer} formula to this:

If IsNull({JOBXREF.ENGINEER})
or Trim({JOBXREF.ENGINEER}) = ""
Then "N/A"
Else {JOBXREF.ENGINEER}

The fact that you're getting 'False' returned means that there are no instances that meet this condition:

If Left({@Engineer},3)
Like
Left({ENGINEERS_NAMES.ENGINEER_INITIALS},3)

If you place the {@Engineer} formula itself on the report, and also place {ENGINEERS_NAMES.ENGINEER_INITIALS} next to it, they should prove this by not looking the same.

Naith

 

[RachelK]

Naith,

They are the same as I took the data from the Main database run a distinct query to make a table in another database and added the full the full names of the engineers they are both the same lenght 3 characters I think the reason I am getting this problem is because I am using two databases.

 

[Naith]

If you've just tested that {Database1.JOBXREF.ENGINEER} and {Database2.ENGINEERS_NAMES.ENGINEER_INITIALS} come up with the same values on the same report successfully, then your problem should not be that you are linking across two databases.

(Am I misunderstanding which fields come from different databases?)

Drop a formula on your report stipulating "Left ({@Engineer},3)" and another saying "Left({ENGINEERS_NAMES.ENGINEER_INITIALS},3)", and see that these are the same. Because you just said that this comparison came up with False.

Naith

 

[lyanch]

I have found that *sometimes* even though *I* think the datatype is the same between two databases, crystal doesn't always agree. I have had this problem even with simple datatypes like int's. It may well be that you have this problem. It can be overcome. Create a subreport and send in the engineers name as the linking parameter. Use this in your selection formula to the access db. Your display formula would be something like iif(isnull(access.name), otherdb.name, access.name)

Lisa