I have the following code that splits a multi valued media_id and selects two other distinct fields and inserts into another table.</SPAN>
Table before and after splitting looks like this</SPAN>
<TBODY>
</TBODY>
The insert into table is the table after splitting. The problem that I am facing with my code is that the media with a elab_count_space of zero is not having any last_written date inserted into the destination table. It’s only inserting the last written date where the elab_count_spaces is greater that zero. Could someone help out so that I can have the last written date selected with split elab_media_id and elab_hostname and inserted into destination table for both zero elab_count spaces and greater than o elab_count-spaces?
The code is below
Option Compare Database
Public Sub Split_Media()
Dim varMedia As Variant
Dim intIndex As Integer
Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
Dim strHost As String
Dim strSQL As String
DoCmd.SetWarnings False
'Insert host names with single media id
strSQL = "INSERT INTO Split_Elab_Hostname_Media_ID(Elab_Hostname, Elab_Media_Id, Last_Written) SELECT GTI_Host_Name_Media.Elab_Hostname, GTI_Host_Name_Media.Elab_Media_Id, TI_Host_Name_Media.Last_Written FROM GTI_Host_Name_Media WHERE GTI_Host_Name_Media.elab_count_spaces=0"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Single Media Processed"
' set up a updateable recordset of your table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Distinct Elab_Hostname, Elab_Media_Id, Last_Written FROM GTI_Host_Name_Media WHERE elab_count_spaces>0", dbOpenDynaset)
Dim newRST As Recordset
Set newRST = dbs.OpenRecordset("Split_Elab_Hostname_Media_ID")
' find number of records in your recordset
Counter = rst.RecordCount
If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not rst.EOF
strHost = rst!Elab_HostName
varMedia = Split(rst!Elab_Media_ID, " ")
For intIndex = LBound(varMedia) To UBound(varMedia)
newRST.AddNew
newRST!Elab_HostName = strHost
newRST!Elab_Media_ID = varMedia(intIndex)
newRST!Last_Written = DateTime
newRST.Update
Next
rst.MoveNext
'Counter = Counter - 1
Loop
End If
MsgBox "Done!"
' Code to parse using space
'varMedia = Split("A29399 E209203 V093020", " ")
'For intIndex = LBound(varMedia) To UBound(varMedia)
' MsgBox varMedia(intIndex)
' Next
End Sub
Thanks
</SPAN></SPAN>
Table before and after splitting looks like this</SPAN>
| Table before splitting the Elab_Media_ID</SPAN> | | | |
| Elab_Hostname</SPAN> | Last_Written</SPAN> | Elab_Media_ID</SPAN> | Elab_Count_Spaces</SPAN> |
| mfn3asff1-infra_1145764335</SPAN> | 4/22/2006 11:52</SPAN> | 183962</SPAN> | 0</SPAN> |
| mfn3asff1-infra_1162093523</SPAN> | 10/28/2006 11:45</SPAN> | 215431 215678 </SPAN> | 1</SPAN> |
| pbglsapp2-h_1163952140</SPAN> | 11/19/2006 11:02</SPAN> | 212117 214964 214972</SPAN> | 2</SPAN> |
| | | | |
| Table after splitting the Elab_Media_ID</SPAN> | | | |
| mfn3asff1-infra_1162093523</SPAN> | 10/28/2006 11:45</SPAN> | 215431</SPAN> | 1</SPAN> |
| mfn3asff1-infra_1162093523</SPAN> | 10/28/2006 11:45</SPAN> | 215678</SPAN> | 1</SPAN> |
| pbglsapp2-h_1163952140</SPAN> | 11/19/2006 11:02</SPAN> | 212117 </SPAN> | 2</SPAN> |
| pbglsapp2-h_1163952140</SPAN> | 11/19/2006 11:02</SPAN> | 214964</SPAN> | 2</SPAN> |
| pbglsapp2-h_1163952140</SPAN> | 11/19/2006 11:02</SPAN> | 214972</SPAN> | 2</SPAN> |
<TBODY>
</TBODY>
The insert into table is the table after splitting. The problem that I am facing with my code is that the media with a elab_count_space of zero is not having any last_written date inserted into the destination table. It’s only inserting the last written date where the elab_count_spaces is greater that zero. Could someone help out so that I can have the last written date selected with split elab_media_id and elab_hostname and inserted into destination table for both zero elab_count spaces and greater than o elab_count-spaces?
The code is below
Option Compare Database
Public Sub Split_Media()
Dim varMedia As Variant
Dim intIndex As Integer
Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
Dim strHost As String
Dim strSQL As String
DoCmd.SetWarnings False
'Insert host names with single media id
strSQL = "INSERT INTO Split_Elab_Hostname_Media_ID(Elab_Hostname, Elab_Media_Id, Last_Written) SELECT GTI_Host_Name_Media.Elab_Hostname, GTI_Host_Name_Media.Elab_Media_Id, TI_Host_Name_Media.Last_Written FROM GTI_Host_Name_Media WHERE GTI_Host_Name_Media.elab_count_spaces=0"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Single Media Processed"
' set up a updateable recordset of your table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Distinct Elab_Hostname, Elab_Media_Id, Last_Written FROM GTI_Host_Name_Media WHERE elab_count_spaces>0", dbOpenDynaset)
Dim newRST As Recordset
Set newRST = dbs.OpenRecordset("Split_Elab_Hostname_Media_ID")
' find number of records in your recordset
Counter = rst.RecordCount
If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not rst.EOF
strHost = rst!Elab_HostName
varMedia = Split(rst!Elab_Media_ID, " ")
For intIndex = LBound(varMedia) To UBound(varMedia)
newRST.AddNew
newRST!Elab_HostName = strHost
newRST!Elab_Media_ID = varMedia(intIndex)
newRST!Last_Written = DateTime
newRST.Update
Next
rst.MoveNext
'Counter = Counter - 1
Loop
End If
MsgBox "Done!"
' Code to parse using space
'varMedia = Split("A29399 E209203 V093020", " ")
'For intIndex = LBound(varMedia) To UBound(varMedia)
' MsgBox varMedia(intIndex)
' Next
End Sub
Thanks
</SPAN></SPAN>
