asked on

Nested Lookup Function

I have a function to lookup the Index from the table "Period"

Function Period(IDate As Date) As Integer

Period = DLookup("Index", "Period", "Periodstart= #" & DMax("Periodstart", "Period", "Periodstart<= #" & IDate & "#") & "#")

End Function

The table is

Index      Periodstart
1      28/08/2004
2      29/09/2004
3      30/10/2004

The function's purpose is to return the index value relative to IDate eg if Idate is 23/9/2004 then it would return 1, if it were 5/10/2004 it would return 2 etc.

Note they are UK format dates.

I can't quite get it to work - help please.

flavo

DLookup("Index", "Period", "Periodstart= DMax('Periodstart', 'Period', 'Periodstart<= #" & IDate & "#")

Jim Horn

Public function fn_getmaxdate()

dim cn as adodb.conneciton
set cn = currentproject.connection

Dim rs as adodb.recordset
set rs = new adodb.recordset

rs.Open "SELECT Max([PeriodStart]) as [MaxDate], First([PeriodStart]) AS [value] FROM TableName", cn

fn_getmaxdate = rs![value]

end function

AndrewMoss

ASKER

I am getting syntax errors with Flavo's answer.

kemp_a

I think flavo's answer is basically correct, although there are syntax problems, try:

DLookup("Index", "Period", "Periodstart=" & DMax("Periodstart", "Period", "Periodstart<= #" & IDate & "#")

Cheers

ASKER CERTIFIED SOLUTION

flavo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndrewMoss

ASKER

Finally got to the answer.

DLookup("Index", "Period", "Periodstart=#" & DMax("Periodstart", "Period", "Periodstart<= #" & IdateUS & "#") & "#")

Flavio is basically correct but missed a few extra #