DLookup with a Date

[scarlton via AccessMonster.com]

I have a two tables; One that holds Pay Period information with [Pay],
[StartDate], and [EndDate] as the 3 fields and a Table for Timesheet with 3
fields, [Pay], [Date] and [Hrs] information. The user will enter a Date along
with the hours they worked in the timesheet table but I need to use a DLookup
to bring in the [Pay] field from the PayPeriod table into the [Pay of the
timesheet table based on the date that is entered in the timesheet table.

Since there are normally 26 pays in a year The [Pay] field will be 1-26 with
the start of that pay period in the [StartDate] and the end of that pay
period in the [EndDate]

I'm somewhat familiar with the DLookup but it has to be something to the
effect that: If [Date] <= [BegDate] and >= [StartDate], get the number in the
[Pay] field from the pay period table and put it in the [Pay] field on the
timesheet table. Any ideas how to code this?

 

[Guest]

You need to create a query first. Then call dlookup function from that query
instead of from your table.

Create query called myquery:
SELECT tblpay.Pay, tblpay.Begdate, tblpay.EndDate
FROM tblpay
WHERE (((tblpay.Begdate)<=#2/17/2007#) AND ((tblpay.EndDate)>=#2/17/2007#));

This query should return only one record.

Then create dlookup function:
mypay = dlookup("pay","myquery")

hope this helps.

Restu Kresnadi - columbus, OH

 

[John W. Vinson]

I have a two tables; One that holds Pay Period information with [Pay],
[StartDate], and [EndDate] as the 3 fields and a Table for Timesheet with 3
fields, [Pay], [Date] and [Hrs] information. The user will enter a Date along
with the hours they worked in the timesheet table but I need to use a DLookup
to bring in the [Pay] field from the PayPeriod table into the [Pay of the
timesheet table based on the date that is entered in the timesheet table.

Since there are normally 26 pays in a year The [Pay] field will be 1-26 with
the start of that pay period in the [StartDate] and the end of that pay
period in the [EndDate]

I'm somewhat familiar with the DLookup but it has to be something to the
effect that: If [Date] <= [BegDate] and >= [StartDate], get the number in the
[Pay] field from the pay period table and put it in the [Pay] field on the
timesheet table. Any ideas how to code this?

You can do this without using the inefficient DLookUp() *at all*. It's
a bit obscure, but you can use what's called a "non-equi join" query.

UPDATE [timesheet] INNER JOIN [PayPeriod]
ON [Timesheet].[Date] >= [PayPeriod].[StartDate]
AND [Timesheet].[Date] <= [PayPeriod].[EndDate]
SET [Timesheet].[Pay] = [PayPeriod].[Pay];

Note that Date is a reserved word and should not be used as a
fieldname; and ideally one would not store the [Pay] field redundantly
in the Timesheet table, but instead use this non-equi join in a SELECT
query to calculate the total pay.

John W. Vinson [MVP]

 

[scarlton via AccessMonster.com]

Hi Restu,
Thanks for your input. This solution works assuming that the supervisor is
entering the timesheet info the week of that current the pay period. I need
to be able to type in any date (even from last year) and have it reference
the Pay Period table to get the Pay # that is relevent to the date that is
entered. Our timesheet system is very non-traditional which means we need the
flexibility to enter and adjust previous time entries and not have the pay
period reflect what it would be at the time of the adjustment.

Thanks
Steve

 

[scarlton via AccessMonster.com]

Hi John,
This is neat and it works great. I have two questions:
1) I created a button on the form to run the update but there is an access
message that pops up to confirm the modification. Can I disable this message.
2) Is there any way to run the query so that it updates the PayPeriod only
for the employee whose time is being entered. When the query is ran, it
updates the entire timesheet table when it really only needs to update the
records for the timesheet that is entered. This module is for our Youth
program so not all of our kids work every week plus we have kids come in and
out of the program so there's no point updating records for kids that have
been exited out of the program.

Thanks
Steve

 

[John W. Vinson]

1) I created a button on the form to run the update but there is an access
message that pops up to confirm the modification. Can I disable this message.

A couple of ways; either put a line

DoCmd.SetWarnings False

before running the query, and (be sure to!!) put

DoCmd.SetWarnings True

after it; or else create a Querydef object and use the Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
<<build your SQL string>>
Set db = CurrentDb
' create an unnamed temporary query
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute, dbFailOnError

2) Is there any way to run the query so that it updates the PayPeriod only
for the employee whose time is being entered.

Sure. Include the employeeID as a criterion. You didn't post your
query (just the DLookUp) so I don't know just how you'ld do that
though. If you need help just post the SQL of the query.

John W. Vinson [MVP]