Solved

Nested Lookup Function

Posted on 2004-10-01
6
233 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 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