subform controls

[donnan33]

I used the above and it is only returning the total from the starttime/timeout1 fields. i keep getting 300 mins which is only 5 hours when I have entered time that should total 8 hours. Sorry to keep bothering you but I am stumped and not so good with this field. No error came back either.



i had entered to test.
starttime - 7am
timeout1 - 12 pm
timein1 - 1pm
timeout2 - blank
timein2 - blank
finish time - 4pm

if i put a time in the timeout2 and timein2 i get the correct total. It's only when I leave those 2 blank that it will not total right......is there someway to make a default value of 0 hours for those fields? when i put 00:00 it returns 12:00.

[donnan33]

I tried the foloowing code:
Total Hours: nz(DateDiff("n",[starttime],[timeout1]),0)+nz(DateDiff("n",[timein1],[timeout2]),0)+nz(DateDiff("n",[timein2],[finishtime]),0)

this only returns total between startime and timout1......what can i do/change to get the total for all fields.

If I put a time in the timeout2 and time in 2 fields it works but it still doesn't if they are blank...? thanks again for all your help.....

[June7]

Okay, gets complicated. If nothing in timeout1 then subtract timeout2 from starttime, if nothing in timeout2, subtract finishtime from starttime. Do similar with finishtime and timein1 and timein2 and starttime and then add.

Try:
TotalHours: (Nz(DateDiff("n",[starttime],Nz([timeout1],nz([timeout2],[finishtime]))),0) + Nz(DateDiff("n",Nz([timein2],Nz([timein1],[starttime])),[finishtime]),0)) / 60

[donnan33]

Not sure if you know anything about tiome calculations in excel. Do you know the formula to do the same in excel? below is en example. I can get the total to calculate right if i have data in every cell. but if i just have in the below cells it does not work right....I need to get total if they only have the below 2 punches and/or they have punches in every cell.

TIME STARTTIME OUTTIME INTIME OUTTIME INTIME FINISHTOTAL HOURSNET PAYABLE HOURS7:00 AM11:00 AM4.0011.00

[June7]

I know how to calculated date/time in Excel. The issue is the variability in filling in the fields as allowed by the data structure. If Jan has a start time of 7:00 AM and doesn't check out until 4:00 PM, that will complete the first in/out pair and nothing in the rest of the columns. Why skip all the way down to Finsh to fill in the only Out? A more normalized table structure would be:

RecID | EmpID | In | Out
1 | J | 01/15/2011 07:00 AM | 01/15/2011 11:00 AM
2 | J | 01/15/2011 12:00 PM | 01/15/2011 04:00 PM

Data from an old fashioned punch clock would probably be more like:

RecID | EmpID | TimePunch | InOut
1 | J | 01/15/2011 07:00 AM | In
2 | J | 01/15/2011 11:00 AM | Out
3 | J | 01/15/2011 12:00 PM | In
4 | J | 01/15/2011 04:00 PM | Out

Having to test if data is provided seems even more complicated in Excel. There is an ISBLANK function that could be use with an IF expression.

[donnan33]

back to access, what you gave me above works fine unless they have just time in start and finsih fields then is does not calculate correctly. I can work with that if there is no other way.

I also have another question..... I have the following code
DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'"

I have a form with a command button to print a report after the user enter the time. there is a date field on the form called date entered. the user enters their daily time for each day. after they enter teach days time I am trying to get it to print my report based on the current user name date. Right now with the above code, it pulls the correct name but is printing every date.....how can I get it to take the current date entered and user and print only that on report without have to have them retype the date and pull it from a query?

[June7]

First, should use employee ID, not name, as criteria. What if you have two Jane Smith? Second, WHERE condition can have multiple criteria.

"employeeID=" & Me.cboemployeeid & " AND dateentered=#" & Me.dateentered & "#"

[donnan33]

DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'" and "dateentered = #" & Me.DateEntered & "#"

I tried the above. now I get a type mismatch runtime error '13' ???

[donnan33]

Private Sub PrintTimeSheet_Click()


DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'" & "dateentered=#" & Me.DateEntered & "#"


End Sub

this is how it looks in vba editor

[June7]

Look at my example again. You are missing the AND operator.

[donnan33]

DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'" And "dateentered = #" & Me.DateEntered & "#"

I still get the type mismatch runtime error with the above?

[June7]

Do you want to search on employeename field instead of employeeID? Two improperly placed quote marks.

DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "' And dateentered = #" & Me.DateEntered & "#"

[donnan33]

I am wanting to diplay the employee name but it is looking up by employee id. before I added the and date entered part it pciekd employee correctle. Maybe i typed something wrong when adding the rest. At the end it does have quotes, apostrophe then quotes for the employee name part. I then put a space and added the dateentered part.

DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'"

the above is what i had originally which pulled the cuttently picked name for the report. then I added the date entered.... And "dateentered = #" & Me.DateEntered & "#"

all of it

DoCmd.OpenReport "rptprintbyemployeewithtime", acViewReport, , "employeename = '" & Me.cboemployeeid.Column(1) & "'" And "dateentered = #" & Me.DateEntered & "#"

then i did a debug comple check and nothing came up. I go to form and click on the command button I get the runtime error. I can make it work by entering in the query to ask for the date and when they click button they reenter date and it works that way. I was just trying to save the extra step of having to enter the date again. I really aprreciate your help and patience with a fairly new database maker. thanks.

[June7]

The WHERE expression is structured to do search on employee name not employee ID.

Did you fix the AND as I suggested?

Is the dateentered field actually a date/time datatype?

[donnan33]

Yes I fixed the and but I still get the tyoe mismatch error. The dateentered field is a date/time field and is formatted as short date. The form is called EmployeeName. I have put a copy here. It is the command but at the bottom to print report. Right now I have it where they have to reenter the date to get it to come up right.