OK, some sample data....

deal_nbr  month_end   col_1   col_2
12345     0                    100     200
12345     200805          150     0
12345     200804          15      56
12345     200803          34      0
67890     0                    399     0
67890     200805          100     100
67890     200804          788     0
67890     200803          100     100

Etc.

 

The main body of the report has selection criteria of month_end = 0. That will display the bulk of my data.

 

I have a function called "me_to_use" that calculates a value of "month_end" I want to use to lookup col_1, col_2, etc. for that deal. This value will vary by deal number.

 

So the desired result is this:

deal_nbr    month_end    me_to_use    col_1    col_2
12345        0                    200805          150      0
67890        0                    200803          100      100

 

I need some kind of lookup that retrieves a value from this table given a deal_nbr and month_end. I cannot for the life of me find a way to do this. It's ridiculously easy in Access using a DLOOKUP function. I don't understand why Crystal seems to lack what I'd consider very basic data functionality.