Get the Credit Card Rate Charged during that Period

[CoffeNut]

A table has the contains records with the charge rate for each credit card with a start date and end date. The current charge rate started 1/1/2004 with .0450 and has a null end date. This means it is the last and most current rate.

I have a report with records selected Visa and Mastercard cardtypes. The transaction table has the transaction date and the credit type type. How do I find out the rate charged on this transaction?

Attempt 1) I tried linking the table using credit card type but get all the credit card records for that type.
Can the record selection criteria get only the record valid for the credit card transaction date.

Attempt 2) I tried to create a function that will make a SQL query to the Credit Card Table. But could not figure out how to put a SQL command inside the function.

Attempt 3) I tried to develop a SQL Expression. But I don't understand out to initiate the SQL expression for each record and display results on the report line.

Attempt 4) Someone suggested using a SubReport. But I'm not sure yet how it would supply value for each record.

So in general is there a way to do an independent sql lookup on the fly while processing each report line.

 

[vidru]

If you want to go the SQL Expression route, then an expression like this will work (*note* - this is SQL Server syntax, but should be adaptable):
[tt]
(
SELECT T.Rate
FROM CreditCardTypes T
WHERE (T.CardType = "CreditCardTransactions"."CardType") AND
(("CreditCardTransactions"."TransDate" BETWEEN T.StartDate AND T.EndDate)
OR
("CreditCardTransactions"."TransDate" >= T.StartDate AND T.EndDate IS NULL))
)
[/tt]
If you have trouble integrating your table names into this, then please post the structure of your tables, along with some sample data.

-dave

 

[lyanch]

IF you want to avoid the "or"

(
SELECT T.Rate
FROM CreditCardTypes T
WHERE (T.CardType = "CreditCardTransactions"."CardType") AND
((isnull("CreditCardTransactions"."TransDate",T.EndDate) BETWEEN T.StartDate AND T.EndDate)
)

Lisa