snb, I have to leave the separators as commas since a string is built that INSERTs or UPDATEs the database with the info.
After staring at how the code is built it dawned on me that the places where I needed to pass a string I surrounded the data by a single quote. So, by changing my variable data types to String, adding a data check to replace an empty string with zero, I could export the .Formula of the cell contents and pass the data to my db as planned.
Here's a snippet of what I did. First is to make the data types String instead of Single:
Private Sub cmdCommit_Click()
'Save button
Dim sSQL As String, dtDate As Date, sDayOfWeek As String, sName As String
Dim sTSWYCAMSTickets As String, sTSWReviewTools As String
Dim sQA As String, sMeetings As String, sOtherWork As String
Dim sBreak As String, sNotes As String, i As Integer
...
Set goConn = CreateObject("ADODB.Connection")
Set goRST = CreateObject("ADODB.Recordset")
goConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & TIME_TRACKING_DB & _
";Persist Security Info=False;"
...
Get the .Formula of the cell. This is the magic that works; I'm not an Excel MVP so I'll let them explain how it works. Next, an empty string will create an error for this code so turn an empty string into a zero:
For i = 0 To miEORCount
With ActiveCell
dtDate = .Offset(i, 0).Value
sDayOfWeek = .Offset(i, 1).Value
sName = Sheet1.cboAgent.Value
sTSWYCAMSTickets = IIf(Len(.Offset(i, YCAMS_CELL).Formula) = 0, "0", .Offset(i, YCAMS_CELL).Formula)
sTSWReviewTools = IIf(Len(.Offset(i, REVIEW_CELL).Formula) = 0, "0", .Offset(i, REVIEW_CELL).Formula)
sOtherWork = IIf(Len(.Offset(i, OTHER_CELL).Formula) = 0, "0", .Offset(i, OTHER_CELL).Formula)
sMeetings = IIf(Len(.Offset(i, MEET_CELL).Formula) = 0, "0", .Offset(i, MEET_CELL).Formula)
sQA = IIf(Len(.Offset(i, QA_CELL).Formula) = 0, "0", .Offset(i, QA_CELL).Formula)
sBreak = IIf(Len(.Offset(i, BREAK_CELL).Formula) = 0, "0", .Offset(i, BREAK_CELL).Formula)
sNotes = "'" & Replace(.Offset(i, NOTES_CELL).Value, "'", "''") & "'"
End With
Then the rest of the code:
sSQL = "SELECT [date], [name]"
sSQL = sSQL & " FROM " & TBL_TT
sSQL = sSQL & " WHERE date = #" & dtDate & "#"
sSQL = sSQL & " AND name = '" & sName & "'"
goRST.Open sSQL, goConn, adOpenDynamic, adLockBatchOptimistic, adCmdText
If goRST.BOF And goRST.EOF Then
sSQL = "INSERT INTO [" & TBL_TT & "]"
sSQL = sSQL & " ([date],[day of week],[name],"
sSQL = sSQL & " [time spent working ycams tickets],"
sSQL = sSQL & " [time spent working in review tools],"
sSQL = sSQL & " [other / work activity],"
sSQL = sSQL & " [meetings],"
sSQL = sSQL & " [qa],"
sSQL = sSQL & " [break],"
sSQL = sSQL & " [notes])" & vbCrLf
sSQL = sSQL & " VALUES (#" & dtDate & "#,"
sSQL = sSQL & "'" & sDayOfWeek & "',"
sSQL = sSQL & "'" & sName & "',"
sSQL = sSQL & sTSWYCAMSTickets & ","
sSQL = sSQL & sTSWReviewTools & ","
sSQL = sSQL & sOtherWork & ","
sSQL = sSQL & sMeetings & ","
sSQL = sSQL & sQA & ","
sSQL = sSQL & sBreak & ","
sSQL = sSQL & sNotes & ")"
goConn.Execute sSQL
Else
sSQL = "UPDATE [" & TBL_TT & "]" & vbCrLf
sSQL = sSQL & " SET [time spent working ycams tickets] = " & sTSWYCAMSTickets & ","
sSQL = sSQL & " [time spent working in review tools] = " & sTSWReviewTools & ","
sSQL = sSQL & " [other / work activity] = " & sOtherWork & ","
sSQL = sSQL & " meetings = " & sMeetings & ","
sSQL = sSQL & " qa = " & sQA & ","
sSQL = sSQL & " training = 0,"
sSQL = sSQL & " break = " & sBreak & ","
sSQL = sSQL & " notes = " & sNotes & vbCrLf
sSQL = sSQL & " WHERE date = #" & dtDate & "#"
sSQL = sSQL & " AND [day of week] = '" & sDayOfWeek & "'"
sSQL = sSQL & " AND name = '" & sName & "'"
goConn.Execute sSQL
End If
goRST.Close
Next i
goConn.Close
Set goRST = Nothing
Set goConn = Nothing
Bookmarks