control based on query in report 1

[vttech]

I have a bounded report (rptBiWeekly) based on the following query

SELECT *
FROM tblLogInOut
WHERE employeeID=forms!frmloginout!txtEmployeeID.Value And (workdate Between forms!frmloginout!txtStartDate.value And forms!frmloginout!txtEndDate.value);

that has the following text box
LogIN, LogOut, Site, and Activity. I used =sum(logout-login) in control source to get the total hours worked txt box . But know I want to add a where clause something like

SELECT Sum((logout-login)*24) AS SiteHours
FROM tblLogInOut 
WHERE Site = "Riverside"  and Activity ="work";

In the report footer. I am lost on how I can accomplish this goal with a report.
If I can get an idea or example on how to add the where clause

Can a control be based on a query? If so how?


Newbie in search of knowledge

 

[PHV]

Have you tried the DSum function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vttech]

Ok that worked with

=DSum("([logout]-[login])*24","tblLogInOut","[Site]='Riverside' And [activity]='work'")

But when I try

=DSum("([logout]-[login])*24","tblLogInOut","[Site]='Riverside' And [activity]='work' and [employeeID] ='reports!rptBiWeekly!txtEmployeeID.Value'")

I get an error; How can I correct this code so that I can have more that two AND conditions?

Newbie in search of knowledge

 

[PHV]

I've never played with access reports, so just a guess ...
either:
=DSum("([logout]-[login])*24","tblLogInOut","[Site]='Riverside' AND [activity]='work' AND [employeeID]=[Reports]![rptBiWeekly]![txtEmployeeID]")

or:
=DSum("([logout]-[login])*24","tblLogInOut","[Site]='Riverside' AND [activity]='work' AND [employeeID] ='" & [txtEmployeeID] & "'")

or:
=DSum("([logout]-[login])*24","tblLogInOut","[Site]='Riverside' AND [activity]='work' AND [employeeID] ='" & [EmployeeID] & "'")

But you talked about a report footer, so I really don't know.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886