Question Table Lookup

[LEXCERM]

Hi all,

Name.......Date.......Hours
Fred.......01/02/10......10
Fred.......10/02/10......15
Fred.......20/02/10......20

Basically, I require a method to lookup (bit like excel's vlookup) a date within a range and return the hours. For example:
- a date of 05/02/10 would return 10
- a date of 10/02/10 would return 15
- a date of 23/02/10 would return 20

Many thanks in advance,
Paul.

 

[Peter Reid]

Assuming your table/query is sorted by Date, you can use the dlast function to achieve this, something like;

=Dlast("[Hours]","TableName","[Date]<=dateserial(2010,02,05)")

 

[Rabbie]

You could also use the the Dlookup function

 

[LEXCERM]

Thanks for your reply gents. Much appreciated.

Paul.

 

[LEXCERM]

Hi there.

I've tried DFIRST, DLAST, DLOOKUP but I cannot get this to work. This is what I am using:-

DLookup("[EmployeeHours]", "tbl_Hours", "[EmployeeID]=163 And [DateChange]>=#" & Me.txtDate & "#")

This seems to work one minute but not the next. A bit hit and miss.

Please advise.

Many thanks in advance,
Paul.

 

[Peter Reid]

What format do you show your dates?

"#" & Me.txtDate & "#" will be interpreted as #mm/dd/yyyy# so if you use dd/mm/yyyy, it will fail when the day of the month is between 1 and 12 (and the day number <> month number)