Help needed with DLookUp construction 1

[LD1010]

Thanks for reading my post.

I've been working on a Payroll Db. In one of the queries the pay record information for all active employees for a two week period are returned. The criteria for this query is: [Forms]![frmReportDates3].[PayPeriodID] There are two other controls on this same form, [cboStartDate] and [cboEndDate]. I need to determine if an observed Holiday from an unrelated table, tblHolidays.HolidayDate falls between those two dates, and if it does to return the value 8 (hours), and if not, 0 (hours) in alias [HolidayHrs]. Is there a way to do this using DLookUp??

I’m thinking this can be done, but I’m lost as how to construct this. Any help would be much appreciated.

 

[lameid]

I suspect you want the Dcount function rather than Dlookup to find the number of holidays or DSum to find holiday hours depending on what is stored.

That said all those functions take three paramaeters, the first being the field you want to lookup/count/sum, the second being the source table/query/SQL and the last being additional criteria statement.

So perhaps something like the following? Then multiply the count of days returned by 8 to get the hours...

Dcount("HolidayDate", "tblHolidays", "HolidayDate Between #" & [Forms]![frmReportDates3]!cboStartDate & "# And #" & [Forms]![frmReportDates3]!cboEndDate & "#")

I'm not sure what your circumstance is but generally as much should be put into queries as possible without using the aggregate / "D" functions.

 

[LD1010]

Thanks very much for your help Lameid!!

Worked great.

I'll take to heart your advice about minimizing the use of "D" functions, but in my particular situation I can't think of another work around.

Thanks again, have a great weekend.