Can I convert a SQL type concatenate function into VB code
Can I convert a SQL type concatenate function into VB code
(OP)
I have a project where I need to concatenate data (employee names) from a field in a table where the field for cost codes in the table match the field for costs codes an open form. I have used the following sql in a query based on a module that PHV and MRemou helped me with years ago.
'This is the concatenate of the Employee Names and Hours per Cost Code
Concatenate("SELECT[Employees] & ': ' & [TotalSTHrs] & ', ' & [TotalOTHrs] & ', ' & [TotalDTHrs] WHERE [TblTimeSheetsDailyLogEmployees].[WEID]='" & [TblTimeSheetsDailyLogNotes].[WEID] & "'", Chr(13) & Chr(10))
This is what I am trying to convert it to in the Event Procedure in the form:
Dim strConcat As String 'build return string
Set rstTblTimeSheetsDailyLogEmployees = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogEmployees", dbOpenDynaset)
With rstTblTimeSheetsDailyLogEmployees
If Not .EOF Then
.FindFirst "[WEID]='" & strWEIDCode & "'"
Do While Not .EOF
.MoveFirst
strConcat = strConcat & _
![Employees]
.MoveNext
Loop
End If
.Close
End With
I want to use the strConcat in the following procedure:
Set rstTblTimeSheetsDailyLogNotes = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogNotes", dbOpenDynaset)
With rstTblTimeSheetsDailyLogNotes
.FindFirst "[WEID]='" & strWEIDCode & "'"
If .NoMatch Then Exit Sub Else .Edit
![EmployeesPerCode] = strConcat
.Update
End With
Any help here would be greatly appreciated!
'This is the concatenate of the Employee Names and Hours per Cost Code
Concatenate("SELECT[Employees] & ': ' & [TotalSTHrs] & ', ' & [TotalOTHrs] & ', ' & [TotalDTHrs] WHERE [TblTimeSheetsDailyLogEmployees].[WEID]='" & [TblTimeSheetsDailyLogNotes].[WEID] & "'", Chr(13) & Chr(10))
This is what I am trying to convert it to in the Event Procedure in the form:
Dim strConcat As String 'build return string
Set rstTblTimeSheetsDailyLogEmployees = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogEmployees", dbOpenDynaset)
With rstTblTimeSheetsDailyLogEmployees
If Not .EOF Then
.FindFirst "[WEID]='" & strWEIDCode & "'"
Do While Not .EOF
.MoveFirst
strConcat = strConcat & _
![Employees]
.MoveNext
Loop
End If
.Close
End With
I want to use the strConcat in the following procedure:
Set rstTblTimeSheetsDailyLogNotes = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogNotes", dbOpenDynaset)
With rstTblTimeSheetsDailyLogNotes
.FindFirst "[WEID]='" & strWEIDCode & "'"
If .NoMatch Then Exit Sub Else .Edit
![EmployeesPerCode] = strConcat
.Update
End With
Any help here would be greatly appreciated!
RE: Can I convert a SQL type concatenate function into VB code
CODE
SET EmployeesPerCode = Concatenate("SELECT [Employees] FROM tblTimeSheetsDailyLogEmployees WHERE [WEID]='" & [WEID] & "'", Chr(13) & Chr(10))
Duane
Hook'D on Access
MS Access MVP
RE: Can I convert a SQL type concatenate function into VB code
I was using this in a make table query which was very slow. The update query is much better.
Thanks for your help.
Johnnycat