If then formula problem

[munchen]

I am using crystal 8.5 and connecting via ODBC to a Microsoft Access database.

I have three tables:
Debts
Allocated
Unallocated

The tables all contain a debt id field and table debts is linked to the other two tables using this field.

In the details section I need to display a a formula field called Date. This field is taken from either table Allocated (Allocated Date) or table Unallocated(Unallocated Date).

What code could i use to get this to display both Allocated and Unallocated dates? For some debts there could be hundreds of both while for others there could be only allocated or only unallocated.

 

[Madawc]

You haven't said what criteria you use to choose a date. If you group by debt id, you could then get the maximum for each type of date for that group. Then do an isnull test to see which applies.

Madawc Williams (East Anglia, UK)

 

[lbass]

It is unclear what you want your display to be. Do you want to show all dates for both allocated and unallocated statuses? Or do you only want to show the most recent date of each? Or the most recent one regardless of status? A sample display would help.

Or are you asking how to link the tables? I think you need a left join from Debts to each of Allocated and Unallocated.

-LB

 

[munchen]

I want to show all dates for both allocated and unallocated for each debt id.

EG

DebtID 201113

Date Debit Credit Reason
05/01/05 62.11 Allocated
06/01/05 62.11 Unallocated
07/01/05 54.00 Allocated

As you can see this displays both allocated and unallocated dates. The reason field shows which is allocated and which is unallocated.

 

[synapsevampire]

I think that your problem is that you're experiencing some row inflation.

What LB meant was for you to post example data, and the expected output. You're too close to the problem which makes it difficult to describe it to those unfamiliar with it.

Show what is in the database, and what the results should be. You state that you want both the allocated and the unallocated in the first post, then later complain because you get both.

-k

 

[MrBillSC]

Try creating a section b in your detail section. Place the allocated table info in section a and your unallocated table info in section b. Suppress either section if the data is null (no record found). Unfortunately you will not be able to sort by both dates with this solution.

MrBill