linking on Calculated fields V8.5

[tomk01]

Is there a way to link two tables in the same database on a calculation?

I have on table that looks like this:

FirstName LastName
Smith John
... ...

The other Table that looks like this:

Desc
pay-Smith, John
Pay-..., ...

is there a way to link these two tables?

 

[PhilPPhillips]

Create a view on the second table, breaking up the pay name into two fields (firstname and lastname), and then on the report link the first table to the view using these fields.

 

[lbass]

Or you could insert a subreport using the Desc table, and create two formulas:

//{@lastname}:
mid(split({desc.field},",")[1],instr({desc.field},"-")+1)

//{@firstname}:
trim(split({desc.field},",")[2])

Then link the subreport to the main report by linking each of these formulas with its corresponding database field in the main report.

-LB

 

[tomk01]

I don't understand how the split function works. neither of those formulas work. please help.

Also i am using an access 97 data base.

 

[lbass]

Instead of saying they don't work, you need to explain the error message you get or provide a sample of the incorrect results, so we can see why and adjust accordingly. Are there variations in the field in the Desc table that you haven't shown us?

-LB

 

[tomk01]

here is is the output on the last name
John Smith

Here is the output on the first name

Error Message:
A subscript must be between 1 and the size of the array.

After getting the error message i changed the [2] to a [1] so my formula now reads:

trim(split({SSbEntries.descr},",")[1])

and the output is
Pay-John Smith

 

[lbass]

My formulas work when the output looks like your first sample:

pay-Smith, John

Your results indicate that there is variation in the output of that field. The {@lastname} and {@firstname} results are exactly what you would expect if the field is, in fact:

pay-John Smith

So, you need to provide a sample of results that shows all the possible variations in the output for this field, before we take this any further.

-LB