Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Nested Lookup Function

Posted on 2004-10-01
6
Medium Priority
?
251 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 66

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

971 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