Join Field Data and Time/Date Calculations

M

Marnus

Hi

Is there a way to join 2 fields into one field using VBA
or Query?
Also.
I wrote a module that queries an SQL table(s) and then
write the data in a certian order into another table.
I need to calculate date and time diferences form the
queried table and then write the result into
the "written" table. I have heard about DATEDIFF but how
do you calculate the diff between Time/StartDate and
Time/EndDate? I dont really want to use a seperate table
to do the calc and then write that value... Any
sugestions? Below is my code...

Sub main()
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim rst3 As ADODB.Recordset
Dim sql As String
Dim name As String
Dim category As String
Dim priority As String
Dim outline_level As String



sql = "SELECT dbo_CallLog.CustID, dbo_CallLog.CallCat,
dbo_CallLog.Priority, dbo_CallLog.CallID,
dbo_Asgnmnt.Assignee, dbo_CallLog.ShortCallDesc,
dbo_Subset.CallerNm, dbo_Subset.CallerPh,
dbo_Subset.CntctMobile, dbo_Asgnmnt.GroupName,
dbo_Asgnmnt.AsgnPhone, dbo_Asgnmnt.AsgnCellPhone,
dbo_CallLog.CallState, dbo_Asgnmnt.TimeSpentTOTAL,
dbo_CallLog.RecvdDate FROM dbo_Asgnmnt INNER JOIN
(dbo_CallLog INNER JOIN dbo_Subset ON dbo_CallLog.CallID
= dbo_Subset.CallID) ON dbo_Asgnmnt.CallID =
dbo_Subset.CallID WHERE (((dbo_CallLog.CallState)='W-I-
P'))ORDER BY dbo_CallLog.CustID, dbo_CallLog.CallCat,
dbo_CallLog.Priority"
sql2 = "SELECT * FROM Heat_All"
sql3 = "Insert into Heat_All name ="
OutlineA = 1
OutlineB = 2
OutlineC = 3
OutlineD = 4



Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set rst3 = New ADODB.Recordset

rst1.ActiveConnection = CurrentProject.Connection
rst2.ActiveConnection = CurrentProject.Connection
rst3.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenStatic
rst2.CursorType = adOpenStatic
rst1.Open sql, options:=adCmdText
DoCmd.SetWarnings False

Do Until rst1.EOF
If rst1![CustID] <> name Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineA] & "','" &
rst1![CustID] & "')"



DoCmd.RunSQL sql3

End If

If rst1![Callcat] <> category Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineB] & "','" &
rst1![Callcat] & "')"
DoCmd.RunSQL sql3

End If

If rst1![priority] <> priority Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineC] & "','" &
rst1![priority] & "')"
DoCmd.RunSQL sql3

End If

sql3 = "Insert into Heat_All
(outline_level,name,Start,Recource_Name) values ('" &
[OutlineD] & "','" & rst1![Callid] & "','" & rst1!
[RecvdDate] & "','" & rst1![Assignee] & "')"
DoCmd.RunSQL sql3
name = rst1![CustID]
category = rst1![Callcat]
priority = rst1![priority]
rst1.MoveNext
Loop


MsgBox ("Update Complete")

End Sub
 
M

Marnus

I have maneged to sort this out. Thanks
-----Original Message-----
Hi

Is there a way to join 2 fields into one field using VBA
or Query?
Also.
I wrote a module that queries an SQL table(s) and then
write the data in a certian order into another table.
I need to calculate date and time diferences form the
queried table and then write the result into
the "written" table. I have heard about DATEDIFF but how
do you calculate the diff between Time/StartDate and
Time/EndDate? I dont really want to use a seperate table
to do the calc and then write that value... Any
sugestions? Below is my code...

Sub main()
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim rst3 As ADODB.Recordset
Dim sql As String
Dim name As String
Dim category As String
Dim priority As String
Dim outline_level As String



sql = "SELECT dbo_CallLog.CustID, dbo_CallLog.CallCat,
dbo_CallLog.Priority, dbo_CallLog.CallID,
dbo_Asgnmnt.Assignee, dbo_CallLog.ShortCallDesc,
dbo_Subset.CallerNm, dbo_Subset.CallerPh,
dbo_Subset.CntctMobile, dbo_Asgnmnt.GroupName,
dbo_Asgnmnt.AsgnPhone, dbo_Asgnmnt.AsgnCellPhone,
dbo_CallLog.CallState, dbo_Asgnmnt.TimeSpentTOTAL,
dbo_CallLog.RecvdDate FROM dbo_Asgnmnt INNER JOIN
(dbo_CallLog INNER JOIN dbo_Subset ON dbo_CallLog.CallID
= dbo_Subset.CallID) ON dbo_Asgnmnt.CallID =
dbo_Subset.CallID WHERE (((dbo_CallLog.CallState)='W-I-
P'))ORDER BY dbo_CallLog.CustID, dbo_CallLog.CallCat,
dbo_CallLog.Priority"
sql2 = "SELECT * FROM Heat_All"
sql3 = "Insert into Heat_All name ="
OutlineA = 1
OutlineB = 2
OutlineC = 3
OutlineD = 4



Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set rst3 = New ADODB.Recordset

rst1.ActiveConnection = CurrentProject.Connection
rst2.ActiveConnection = CurrentProject.Connection
rst3.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenStatic
rst2.CursorType = adOpenStatic
rst1.Open sql, options:=adCmdText
DoCmd.SetWarnings False

Do Until rst1.EOF
If rst1![CustID] <> name Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineA] & "','" &
rst1![CustID] & "')"



DoCmd.RunSQL sql3

End If

If rst1![Callcat] <> category Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineB] & "','" &
rst1![Callcat] & "')"
DoCmd.RunSQL sql3

End If

If rst1![priority] <> priority Then
sql3 = "Insert into Heat_All
(outline_level,name) values ('" & [OutlineC] & "','" &
rst1![priority] & "')"
DoCmd.RunSQL sql3

End If

sql3 = "Insert into Heat_All
(outline_level,name,Start,Recource_Name) values ('" &
[OutlineD] & "','" & rst1![Callid] & "','" & rst1!
[RecvdDate] & "','" & rst1![Assignee] & "')"
DoCmd.RunSQL sql3
name = rst1![CustID]
category = rst1![Callcat]
priority = rst1![priority]
rst1.MoveNext
Loop


MsgBox ("Update Complete")

End Sub

.
Click to expand...
 
Top