Solved

Nested Lookup Function

Posted on 2004-10-01
6
230 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:AndrewMoss
6 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12200933
DLookup("Index", "Period", "Periodstart= DMax('Periodstart', 'Period', 'Periodstart<= #" & IDate & "#")
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12200976
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
0
 

Author Comment

by:AndrewMoss
ID: 12201350
I am getting syntax errors with Flavo's answer.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 5

Expert Comment

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

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

Cheers

0
 
LVL 34

Accepted Solution

by:
flavo earned 500 total points
ID: 12201436
DLookup("Index", "Period", "Periodstart=" & DMax("Periodstart", "Period", "Periodstart<= #" & IDate & "#"))

Later
0
 

Author Comment

by:AndrewMoss
ID: 12201661
Finally got to the answer.

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

Flavio is basically correct but missed a few extra #
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now