Concatenate strings together (1 Viewer)

[boblife42]

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

 

[boblarson]

One quick question -

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

 

[boblife42]

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.

 

[boblarson]

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)

 

[boblife42]

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.