Using DLOOKUP with >= operator?

[SydneyGeek]

Hi, I have a DLOOKUP question. How do you set up DLOOKUP to work like VLOOKUP in Excel, or don't you?
Example:
Table1 -- Levy
EffectiveDate LevyRate
1-Jan-02 2%
1-Jan-03 4%
1-Jan-04 3.5%

Table2
TransDate Other Fields
Range of transactions, varying dates.

I'd like to build an expression to place the correct effective dates against each transaction date. Have tried

=DLookUp("[LevyRate]","Levy","[Effective Date] >=" & [Transdate])
=DLookUp("[LevyRate]","Levy","[Effective Date] >=" & "[Transdate]")
=DLookUp("[LevyRate]","Levy","[Effective Date] >= [Transdate]")

I think the first option is closest, but still no dice -- I get 0 values for all records. Can the > or >= operators be used with DLOOKUP, or do I need another approach?

Thanks in advance
Denis

 

[Norie]

Are the effective date and transaction date linked by year?

 

[SydneyGeek]

Hi Norie,
No, there is no link between the tables. I often use this as a way to populate a query with a single value -- sort of a global variable.

I'm currently trying a different approach, using the OnCurrent and AfterUpdate events of the form to (a) load the values into unbound controls and (b) populate the LevyPercent field if the Transaction Date is >= EffectiveDate. So far, it seems to be working but the users of this db have an uncanny knack of stuffing things up, so I was hoping for a calculation-based approach.

Denis

 

[Norie]

I didn't mean a link between tables.

I meant something like if the year of the effective date is the same as that of the transaction date then use that levy rate.

I was thinking of using the Year function in a query.

e.g.

SELECT [Trans Table].TransDate, [Levy Table].Levy
FROM [Trans Table] INNER JOIN [Levy Table] ON Year([Trans Table].TransDate) = Year([Levy Table].EffectiveDate);

 

[SydneyGeek]

Ahh... OK, I misunderstood where you were coming from.

Unfortunately I need to match the whole date because the levy is likely to change on a monthly basis.

Denis

 

[Norie]

How about

SELECT [Trans Table].TransDate, [Levy Table].Levy
FROM [Trans Table] INNER JOIN [Levy Table] ON ([Trans Table].TransDate) < ([Levy Table].EffectiveDate);

 

[SydneyGeek]

Thanks Norie, that's what I need

Denis