Dlookup repeat

[Chris75 via AccessMonster.com]

Hello,

Here is my question. I currently have an unbound field in a form referring
to a query. However, the dlookup is repeating the same information for each
record rather than giving distinct data.

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID])

Prod Q is a query that returns the total amount of hours worked by an
employee in a week. When I run the query, everything is fine. I get the
week #, the total amount of hours and the employee ID. When I have checked
against the schedule, this matches :

SELECT DISTINCTROW Sum(([Schedule]![Time OUT]-[Schedule]![Time IN]-[Schedule]!
[TIME OFF]-[Schedule]![ANOMALY]+[Schedule]![OVERTIME]-1)) AS HRS, Employees.
[EMPLOYEE ID], Format$([Schedule].[WorkDate],'ww') AS WK
FROM Employees INNER JOIN Schedule ON Employees.[EMPLOYEE ID] = Schedule.
[EMPLOYEE ID]
GROUP BY Employees.[EMPLOYEE ID], Format$([Schedule].[WorkDate],'ww');

In a subform (Productivity), I have put the above dlookup in an unbound
textbox labelled HRS. Reason for this is that I want the total amount of
hours for a week to show so that a rate can be calculated. I also want to
ensure that if hours are changed in an employees schedule, this will reflect
automatically in the Productivity subform.

I'm not sure why the =dlookup is repeating the same data for each record when
the query gives me distinct returns based on the underlying data (from a
table called Schedule).

Any ideas?

Thank you!




It works correctly.

 

[Ed Robichaud]

To work properly in the subform context, the criteria (3rd argument) of your
DLookup function needs to reference the [employeeID]value in the subform, so
you'll need to spell it out like: Me!Subform1.Form!ControlName

 

[Chris75 via AccessMonster.com]

Hello Ed,

I have 2 subforms, which should be referenced? I tried Me!Schedule.Form!
[EMPLOYEE ID] and I got a Name error.

To work properly in the subform context, the criteria (3rd argument) of your
DLookup function needs to reference the [employeeID]value in the subform, so
you'll need to spell it out like: Me!Subform1.Form!ControlName

Hello,

[quoted text clipped - 38 lines]

It works correctly.

 

[Ed Robichaud]

If I understand you correctly, you're trying to calculate time-period hours
per employee. If you use that DLookup function (that returns data not in
the subform's data source, but restricted to an employeeID that is) then you
need to reference the control that has that value in your subform. If the
employeeID is available in the main form, another option is to do the
calculation there in an invisible unbound control, then use that as the data
source for the control in your subform - like :=Me!Parent!ControlName

-Ed

Hello Ed,

I have 2 subforms, which should be referenced? I tried Me!Schedule.Form!
[EMPLOYEE ID] and I got a Name error.

To work properly in the subform context, the criteria (3rd argument) of
your
DLookup function needs to reference the [employeeID]value in the subform,
so
you'll need to spell it out like: Me!Subform1.Form!ControlName

Hello,

[quoted text clipped - 38 lines]

It works correctly.

 

[KenSheridan via AccessMonster.com]

Your query is grouped by both the EMPLOYEEID and the expression which returns
the computed WK column, so to return a value for a specific employee/week
you'd need to include values for both in the criterion for the DLookup
function call. Something along the lines of:

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID] & " And WK = """ &
[WeekNumber] & """")

where WeekNumber and EMPLOYEE ID are both in the subform's underlying
recordset. Note that as you've used the Format function to return the week
number the data type of the computed WK column will be text, so the value
needs to be wrapped in quotes characters as above. If the subform's
recordset does not include the Week Number, but a date you can return the
week number with the DatePart function, e.g.

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID] & " And WK = """ &
DatePart("ww", [Workdate]) & """")

Your query does assume that all WorkDate values are in the same calendar year
of course as otherwise grouping by week would group rows from the same week
in two or more years together. If the data does span more than one year you
should also group by the Year, e.g. with Year([WorkDate]) and include the
year in the criterion of the DLookup function call in the same way. The Year
function returns an integer number data type BTW, not text.

Ken Sheridan
Stafford, England

Hello,

Here is my question. I currently have an unbound field in a form referring
to a query. However, the dlookup is repeating the same information for each
record rather than giving distinct data.

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID])

Prod Q is a query that returns the total amount of hours worked by an
employee in a week. When I run the query, everything is fine. I get the
week #, the total amount of hours and the employee ID. When I have checked
against the schedule, this matches :

SELECT DISTINCTROW Sum(([Schedule]![Time OUT]-[Schedule]![Time IN]-[Schedule]!
[TIME OFF]-[Schedule]![ANOMALY]+[Schedule]![OVERTIME]-1)) AS HRS, Employees.
[EMPLOYEE ID], Format$([Schedule].[WorkDate],'ww') AS WK
FROM Employees INNER JOIN Schedule ON Employees.[EMPLOYEE ID] = Schedule.
[EMPLOYEE ID]
GROUP BY Employees.[EMPLOYEE ID], Format$([Schedule].[WorkDate],'ww');

In a subform (Productivity), I have put the above dlookup in an unbound
textbox labelled HRS. Reason for this is that I want the total amount of
hours for a week to show so that a rate can be calculated. I also want to
ensure that if hours are changed in an employees schedule, this will reflect
automatically in the Productivity subform.

I'm not sure why the =dlookup is repeating the same data for each record when
the query gives me distinct returns based on the underlying data (from a
table called Schedule).

Any ideas?

Thank you!

It works correctly.

 

[Chris75 via AccessMonster.com]

Thank you Ken and Ed!

Your query is grouped by both the EMPLOYEEID and the expression which returns
the computed WK column, so to return a value for a specific employee/week
you'd need to include values for both in the criterion for the DLookup
function call. Something along the lines of:

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID] & " And WK = """ &
[WeekNumber] & """")

where WeekNumber and EMPLOYEE ID are both in the subform's underlying
recordset. Note that as you've used the Format function to return the week
number the data type of the computed WK column will be text, so the value
needs to be wrapped in quotes characters as above. If the subform's
recordset does not include the Week Number, but a date you can return the
week number with the DatePart function, e.g.

=DLookUp("HRS","Prod Q","[EMPLOYEE ID]=" & [EMPLOYEE ID] & " And WK = """ &
DatePart("ww", [Workdate]) & """")

Your query does assume that all WorkDate values are in the same calendar year
of course as otherwise grouping by week would group rows from the same week
in two or more years together. If the data does span more than one year you
should also group by the Year, e.g. with Year([WorkDate]) and include the
year in the criterion of the DLookup function call in the same way. The Year
function returns an integer number data type BTW, not text.

Ken Sheridan
Stafford, England

Hello,

[quoted text clipped - 31 lines]

It works correctly.