I need to have the following time in/out code and a specific date for a two week period to populate a specific field in a form. The "PayPeriod" is the trigger for the event, I need help with the rest of the code to have the payperiod subtracted from 1 through 13 to get the correct work date. What I need to end up with is a field that looks like 6/22/2008 7:00 AM. I think that I need to format the time in/out and the date to strings and then concatenate them, I just do not know the steps to go about it.
Any help would be appreciated.
Thank you



Private Sub PayPeriod_AfterUpdate() Dim rs As Recordset, " _
Dim strSQL As String Dim i As Integer Dim strDayNum As String, " _
Dim strDayAMIn As String Dim strDayPMIn As String, " _
Dim strDayAMOut As String Dim strDayPMOut As String, " _
Dim strFldWorkdayWorked As String Dim strTemp As String , " _
'Query the employee table for the employee in question, " _
strSQL = "Select * from tbl_EmployeeData WHERE [Employee#]='" &, " _
EmployeeNo & "'" Set rs = CurrentDb.OpenRecordset(strSQL), " _
'For each day, check if scheduled to work, " _
For i = 1 To 14 strTemp = "00" & Trim(Str(i)), " _
strDayNum = Right(strTemp, 2) , " _
' if scheduled, then fill in default hours from record, " _
strFldWorkdayWorked = "WKDay" & strDayNum , " _
If rs.Fields(strFldWorkdayWorked) Then , " _
strDayAMIn = "Day" & strDayNum & "InAM" , " _
strDayPMIn = "Day" & strDayNum & "InPM" , " _
strDayAMOut = "Day" & strDayNum & "OutAM", " _
strDayPMOut = "Day" & strDayNum & "OutPM" , " _
Me.Controls(strDayAMIn).Value = rs!InAmTime , " _
Me.Controls(strDayPMIn).Value = rs!InPmTime , " _
Me.Controls(strDayAMOut).Value = rs!OutAmTime, " _
Me.Controls(strDayPMOut).Value = rs!OutPmTime, " _
End If , " _
Next 'Close recordset rs.Close Set rs = Nothing , " _
DoCmd.RunMacro "mcr_runcalchours"End Sub

One quick question -

Why are you not saving the date and time together? Date/Time functions become much easier if you do.

I can not store the date and time together, the date changes every two weeks the time in/out is a set schedule. That is the main problem, I have to store a changing value with a constant value.

I can not store the date and time together, the date changes every two weeks the time in/out is a set schedule. That is the main problem, I have to store a changing value with a constant value.

Okay, I'm confused. How can you have multiple time in and time out for the same date? So how can you have

6/21/2008 7:00:00 AM 6/21/2008 5:00:00 PM
6/21/2008 7:02:00 AM 6/21/2008 5:00:00 PM
6/21/2008 7:00:00 AM 6/21/2008 5:05:00 PM

for the same person???

I think your design is a bit flawed... You should be storing the date/time together and the two week time card period can be gathered in a different way.

But, to concatenate stuff together for a date:

CDate(WhateverDateFieldHere & " " & WhateverTimeFieldHere)

Hi, thank you for trying to help here is what I came up with and it seems to be working:

Private Sub PayPeriod_AfterUpdate()
Dim rs As Recordset
Dim strSQL As String
Dim i As Integer
Dim strDayNum As String
Dim strDayAMIn As String
Dim strDayPMIn As String
Dim strDayAMOut As String
Dim strDayPMOut As String
Dim strFldWorkdayWorked As String
Dim strTemp As String
Dim DateWorked As String

'Query the employee table for the employee in question

strSQL = "Select * from tbl_EmployeeData WHERE [Employee#]='" & EmployeeNo & "'"

Set rs = CurrentDb.OpenRecordset(strSQL)
'For each day, check if scheduled to work
For i = 1 To 14

strTemp = "00" & Trim(Str(i))
strDayNum = Right(strTemp, 2)

'if scheduled, then fill in default hours from record
strFldWorkdayWorked = "WKDay" & strDayNum

If rs.Fields(strFldWorkdayWorked) And rs!InAmTime > rs!OutAmTime Then

DateWorked = "Day" & strDayNum
strDayAMIn = "Day" & strDayNum & "InAM"
strDayPMIn = "Day" & strDayNum & "InPM"
strDayAMOut = "Day" & strDayNum & "OutAM"
strDayPMOut = "Day" & strDayNum & "OutPM"

Me.Controls(strDayAMIn).Value = Me.Controls(DateWorked) & " " & rs!InAmTime
Me.Controls(strDayPMIn).Value = Me.Controls(DateWorked) + 1 & " " & rs!InPmTime
Me.Controls(strDayAMOut).Value = Me.Controls(DateWorked) + 1 & " " & rs!OutAmTime
Me.Controls(strDayPMOut).Value = Me.Controls(DateWorked) + 1 & " " & rs!OutPmTime


ElseIf rs.Fields(strFldWorkdayWorked) Then

DateWorked = "Day" & strDayNum
strDayAMIn = "Day" & strDayNum & "InAM"
strDayPMIn = "Day" & strDayNum & "InPM"
strDayAMOut = "Day" & strDayNum & "OutAM"
strDayPMOut = "Day" & strDayNum & "OutPM"

Me.Controls(strDayAMIn).Value = Me.Controls(DateWorked) & " " & rs!InAmTime
Me.Controls(strDayPMIn).Value = Me.Controls(DateWorked) & " " & rs!InPmTime
Me.Controls(strDayAMOut).Value = Me.Controls(DateWorked) & " " & rs!OutAmTime
Me.Controls(strDayPMOut).Value = Me.Controls(DateWorked) & " " & rs!OutPmTime

End If
Next
'Close recordset
rs.Close
Set rs = Nothing
DoCmd.RunMacro "mcr_runcalchours"

End Sub



I thought I would post the code in case someone else has the same kind of problem.