lookup in access (1 Viewer)

[Isle96]

one table has currency field. second table has currency and dates fields. for each record in currency in 1st table, I have to lookup up that currency in 2nd table and compare date field to date().

 

[Simon C]

First question I would ask is why do you have two tables?

To do a lookup you would need the second table to have unique values in the currency field. If your first table is one of unique currencies then why not combine the two tables?

However if the currency field in the first table does not have unique values then the lookup would be:

Dim strCurrency_To_Lookup As String
Dim dteDate As Date
Dim lngDays_Diff As Long

strCurrency_To_Lookup = *See below

'The Lookup
dteDate = DLookup("[Date]","SecondTable","[Currency] = '" & strCurrency_To_Lookup & "'"

'Give you the difference between the two dates in days
lngDays_Diff = DateDiff("d",dteDate,Date())

Where SecondTable is the name of your second table, [Currency] is the currency field in your second table and strCurrency_To_Lookup is the currency value you want to find. *As I don't know your exact situtation I don't know the best way to get the value you want to find into the string variable.

Hope that helps.