criteria not working

[vttech]

I am trying to get login and logout time as it relates to an employeeid and the Start and End dates of a pay period from table tbllogInOut. The first part of the criteria works fine in regards to the employeeID but I am having trouble with the start and end dates of the pay period. The statement below is what I have in a text box control source

=Format(DSum("(([logout]-[login])*24)","tblLogInOut",[employeeID]=Reports!rptBiWeekly!txtEmployeeID And ([workdate] Between Forms!frmloginout!txtStartDate And Forms!frmloginout!txtEndDate)),"#.00")

This is the portion of the statement that is not functioning because it's giving be the total hours of all data inputted in the table instead of basing it on the criteria I want.

And ([workdate] Between [Forms]![frmloginout]![txtStartDate] And [Forms]![frmloginout]![txtEndDate])

Any ideas on how to resolve this issue?


Newbie in search of knowledge

 

[JohnLowell]

You need to delimit the date criteria with # signs.
Try using this:

And ([workdate] Between #" & [Forms]![frmloginout]![txtStartDate] & "# And #" & [Forms]![frmloginout]![txtEndDate] & "#")

 

[vttech]

john thanks I tried your approach and got syntax error. so I tried

=Format(DSum("(([logout]-[login])*24)","tblLogInOut","[employeeID]=Reports!rptBiWeekly!txtEmployeeID And ([workdate] Between & '#' & Forms!frmloginout!txtStartDate & '#' & And & '#' & Forms!frmloginout!txtEndDate & '#'")),"#.00")

But I am still receiving syntax error message any ideas?

Newbie in search of knowledge

 

[JohnLowell]

Try This:

=Format(DSum("(([logout]-[login])*24)","tblLogInOut","[employeeID]= " & Reports!rptBiWeekly!txtEmployeeID & " And [workdate] Between & #" & Forms!frmloginout!txtStartDate & "# And #" & Forms!frmloginout!txtEndDate & "#"),"#.00")

 

[dhookom]

John's replay has an extra "&" after Between.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JohnLowell]

Thanks dhookom.....

This is the correct one:

=Format(DSum("(([logout]-[login])*24)","tblLogInOut","[employeeID]= " & Reports!rptBiWeekly!txtEmployeeID & " And [workdate] Between #" & Forms!frmloginout!txtStartDate & "# And #" & Forms!frmloginout!txtEndDate & "#"),"#.00")