Help with Criteria on a query please

  • Thread starter Thread starter Jen via AccessMonster.com
  • Start date Start date
J

Jen via AccessMonster.com

I ams truggling with a query and was hoping someone could point me in the
right direction.

SELECT tblTimeCard.EmployeeID, tblTimeCard.TimeCardID,
tblTimeCardHours.WorkCodeID, tblTimeCardHours.Hours,
tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.Dateto,
tblPayRates.PayRateChange, [tblTimeCardHours].[Hours]*[tblPayRates].
[PayRateChange] AS Cost
FROM (tblTimeCard INNER JOIN tblPayRates ON tblTimeCard.EmployeeID =
tblPayRates.EmployeeID) INNER JOIN tblTimeCardHours ON
tblTimeCard.TimeCardID = tblTimeCardHours.TimeCardID
WHERE (((tblTimeCard.DateWorked) Between [tblPayRates].[DateFrom] And
[tblPayRates].[DateTo]));

I need to be able to also introduce a parameter query on the field
tbl.TimeCard.DateWorked. Each Time I do it loses the criteria I already
have in it which is required to gain the PayRateChange from the table
tbl.PayRate.

Any advice would be greatly appreciated. Thanks
 
What do you mean by "introduce a parameter" to the query? I don't see why
this parameter should overwrite your existing criteria unless you're
entering them over the top of it yourself... you should be able to add the
same field again and enter your new parameter in there, which in SQL would
look like:

SELECT tblTimeCard.EmployeeID, tblTimeCard.TimeCardID,
tblTimeCardHours.WorkCodeID, tblTimeCardHours.Hours,
tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.Dateto,
tblPayRates.PayRateChange, [tblTimeCardHours].[Hours]*[tblPayRates].
[PayRateChange] AS Cost
FROM (tblTimeCard INNER JOIN tblPayRates ON tblTimeCard.EmployeeID =
tblPayRates.EmployeeID) INNER JOIN tblTimeCardHours ON
tblTimeCard.TimeCardID = tblTimeCardHours.TimeCardID
WHERE (((tblTimeCard.DateWorked) Between [tblPayRates].[DateFrom] And
[tblPayRates].[DateTo]) AND ((tblTimeCard.DateWorked) = [Please enter Date])
);
 
David said:
What do you mean by "introduce a parameter" to the query? I don't see why
this parameter should overwrite your existing criteria unless you're
entering them over the top of it yourself... you should be able to add the
same field again and enter your new parameter in there, which in SQL would
look like:

SELECT tblTimeCard.EmployeeID, tblTimeCard.TimeCardID,
tblTimeCardHours.WorkCodeID, tblTimeCardHours.Hours,
tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.Dateto,
tblPayRates.PayRateChange, [tblTimeCardHours].[Hours]*[tblPayRates].
[PayRateChange] AS Cost
FROM (tblTimeCard INNER JOIN tblPayRates ON tblTimeCard.EmployeeID =
tblPayRates.EmployeeID) INNER JOIN tblTimeCardHours ON
tblTimeCard.TimeCardID = tblTimeCardHours.TimeCardID
WHERE (((tblTimeCard.DateWorked) Between [tblPayRates].[DateFrom] And
[tblPayRates].[DateTo]) AND ((tblTimeCard.DateWorked) = [Please enter
[Date])
);
Click to expand...

Maybe try this [Please enter Date:]


SELECT tblTimeCard.EmployeeID, tblTimeCard.TimeCardID,
tblTimeCardHours.WorkCodeID, tblTimeCardHours.Hours,
tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.Dateto,
tblPayRates.PayRateChange, [tblTimeCardHours].[Hours]*[tblPayRates].
[PayRateChange] AS Cost
FROM (tblTimeCard INNER JOIN tblPayRates ON tblTimeCard.EmployeeID =
tblPayRates.EmployeeID) INNER JOIN tblTimeCardHours ON
tblTimeCard.TimeCardID = tblTimeCardHours.TimeCardID
WHERE (((tblTimeCard.DateWorked) Between [tblPayRates].[DateFrom] And
[tblPayRates].[DateTo]) AND ((tblTimeCard.DateWorked) = [Please enter
Date:])
 
Back
Top