Report tool in userform

[cken21]

Hi,

I have a userform which collects data and stores it in a database, i have an report tab on the userform which collates the data between two dates. Currently it searches a managers number and returns the number of entries listed under that managers number. I would prefer if it searched by the managers name, could anyone suggest a change to the following code which would allow this?>

'Set Searchstring for Overall Feedback
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbItemsOfFeedback.Value = totalFeedback
Set rs = Nothing

 

[SkipVought]

Do you have a field in your table for manager name?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Yes, i tried it with name first which didnt work so i changed it to number which i knew how to code. I would prefer to use the names.

 

[SkipVought]

This is like pulling teeth...

What field is the manager name field?

What code did you use with name?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Sorry, i am new to both posting on forums and Visual basic. I have posted the entire code below.

Because i couldn't get the report tool to work (Report MI & Report Full 1&2) using the Names i went with numbers 1-10.



Private Sub cbNextSteps_Change()

If cbNextSteps.Value = "Yes" Then
tbNextStepsNotes.Visible = True
LabelNextStepsNotes.Visible = True
ElseIf cbNextSteps.Value = "No" Then
tbNextStepsNotes.Visible = False
LabelNextStepsNotes.Visible = False
End If

End Sub

Private Sub CheckBox1_Click()

If (CheckBox = Yes) Then
[tbCB] = "Yes"
End If

End Sub




Private Sub cmdManagerFull_Click()


Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
Range("A1").Select

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True


End Sub

Private Sub cmdManagerMI_Click()
If tbTDateFrom.Value = "" Then
Call ReportMI2M
Call GetFullReport2M
ElseIf tbTDateFrom.Value <> "" Then
Call ReportMIM
Call GetFullReportM
End If
End Sub


Private Sub cmdReport_Click()
Const pwd As String = "TMFeedback"
If Not FrmSwitchingFeedback.MultiPage1.Pages(1).Visible Then
If InputBox("Enter Password") = pwd Then
FrmSwitchingFeedback.MultiPage1.Pages(1).Visible = True
Else
MsgBox ("Incorrect password")
End If
Else
FrmSwitchingFeedback.MultiPage1.Pages(1).Visible = False
End If
End Sub




Private Sub cmdSearch_Click()
Call FindIt
End Sub

Private Sub CommandButton1_Click()

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True

End Sub



Private Sub CommandButton2_Click()

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True
Workbooks("Switching Feedback.xls").Close SaveChanges:=False

End Sub

Private Sub CommandButton3_Click()

Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
Range("A1").Select

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True

End Sub

Private Sub CommandButton4_Click()

Call SubmitFeedback

End Sub

Private Sub CommandButton5_Click()

If tbDateFrom.Value = "" Then
Call ReportMI2
Call GetFullReport2
ElseIf tbDateFrom.Value <> "" Then
Call ReportMI
Call GetFullReport
End If
End Sub




Private Sub CommandButtonMIClear_Click()

tbPersonnel.Value = ""
tbDateFrom.Value = ""
tbDateTo.Value = ""
tbItemsOfFeedback.Value = ""


End Sub




Private Sub CommandButton9_Click()
Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
Range("A1").Select

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True
End Sub

Private Sub CommandButtonExit_Click()

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True
Workbooks("Switching Feedback.xls").Close SaveChanges:=False

End Sub

Private Sub CommandButtonMinimise_Click()

Unload Me
Application.WindowState = xlMaximized
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Reviewing").Visible = True
Application.CommandBars("Visual Basic").Visible = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True

End Sub

Private Sub MultiPage1_Change()

End Sub



Private Sub UserForm_Initialize()

Application.WindowState = xlNormal
Application.Left = 661.75
Application.Top = -1.25
Application.Width = 300
Application.Height = 150
Application.CommandBars("Drawing").Visible = False
Application.CommandBars("Control Toolbox").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Reviewing").Visible = False
Application.CommandBars("Visual Basic").Visible = False
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False

MultiPage1.Value = 0

'User Text box & Label *Hidden*
tbUser.Visible = False
LabelUser.Visible = False
tbCB.Visible = False
LabelCB.Visible = False
tbUser.Value = GetUserName()
tbFeedbackFor.Value = ""
tbNextStepsNotes.Value = ""
tbNextStepsNotes.Visible = False
LabelNextStepsNotes.Visible = False
MultiPage1.Pages("Page2").Visible = False

DateTo = Date
DateToday = Format$(DateTo, JetDateFormat)
tbDate.Value = DateToday

cbManager.Value = ""
cbProcess.Value = ""
cbError.Value = ""
tbCB.Value = ""
tbAdditionalDetails.Value = ""
cbNextSteps.Value = ""


tbPersonnel.Value = ""
tbDateFrom.Value = ""
tbDateTo.Value = ""
tbTeam.Value = ""
tbTDateFrom.Value = ""
tbTDateTo.Value = ""
tbItemsOfFeedback.Value = ""
tbNoOfBreaches.Value = ""
tbProductNum.Value = ""
tbIncorrectToddasoDetailsEntered.Value = ""
tbIncorrectLetterTypeRequested.Value = ""
tbDidNotFullyCheckApplication.Value = ""
tbIncorrectVariableEntered.Value = ""
tbDiarisedIncorrectly.Value = ""
tbDidNotRequestAllForms.Value = ""
tbNotEndedOnAWD.Value = ""
tbCreditsNotActioned.Value = ""
tbDidNotFullyCheckCustomerNotes.Value = ""
tbFilesNotSplit.Value = ""
tbISAActionedAsSavings.Value = ""
tbDidNotAddNotesCorrectly.Value = ""
tbNotEnteredOnToddaso.Value = ""
tbDidNotCheckIndicators.Value = ""
tbDidNotActionAsManual.Value = ""
tbDidNotLogMobileNumber.Value = ""
tbErrorNotOrderedTwice.Value = ""
tbRejectedIndexing.Value = ""
tbIncorrectBrandOfLetterUsed.Value = ""
tbIncorrectActionTakenOnCase.Value = ""
tbIncorrectLetterUsed.Value = ""
tbIncorrectDiaryDateUsed.Value = ""
tbPPListMissing.Value = ""
tbLetterMissing.Value = ""
tbOther.Value = ""
tbfeedbackfrom.Value = ""

tbInitials.Value = ""
tbTeamS.Value = ""
tbPerS.Value = ""

With cbProcess
.AddItem ("Stage 1")
.AddItem ("Stage 2")
.AddItem ("Stage 3")
.AddItem ("Manuals")
.AddItem ("Research")
End With

With cbError
.AddItem ("Incorrect Toddaso details entered")
.AddItem ("Incorrect Letter Type Requested")
.AddItem ("Did not fully check application")
.AddItem ("Incorrect Variable Entered")
.AddItem ("Diarised Incorrectly")
.AddItem ("Did not request all forms")
.AddItem ("Not ended on AWD")
.AddItem ("Credits not actioned")
.AddItem ("Did not fully check customer notes")
.AddItem ("Files not Split")
.AddItem ("ISA actioned as savings")
.AddItem ("Did not add notes correctly")
.AddItem ("Not entered on Toddaso")
.AddItem ("Did not check indicators")
.AddItem ("Did not action as Manual")
.AddItem ("Did not log Mobile number")
.AddItem ("09013* not ordered twice")
.AddItem ("Rejected-Indexing")
.AddItem ("Incorrect Brand of Letter used")
.AddItem ("Incorrect Action taken on case")
.AddItem ("Incorrect Letter used")
.AddItem ("Incorrect Diary Date Used")
.AddItem ("PP List Missing")
.AddItem ("Letter Missing")
.AddItem ("Other")
End With

With cbManager
.AddItem ("1")
.AddItem ("2")
.AddItem ("3")
.AddItem ("4")
.AddItem ("5")
.AddItem ("6")
.AddItem ("7")
.AddItem ("8")
.AddItem ("9")
.AddItem ("10")

End With


With cbNextSteps
.AddItem ("Yes")
.AddItem ("No")
End With

'Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
'Range("A2:L65000").Select
'Selection.ClearContents

End Sub

Public Function GetUserName()

' Buffer size for the return string.
Const lpnLength As Integer = 255

' Get return buffer space.
Dim Status As Integer

' For getting user information.
Dim lpName, lpUserName As String

' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)

' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)

' See whether error occurred.
If Status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else

' An error occurred.
MsgBox "Unable to get username - System Exiting. Please Contact your Resolver"
End
End If

GetUserName = lpUserName

End Function

Private Sub tbFeedbackFor_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.tbFeedbackFor.Text, "-") > 0 Or Me.tbFeedbackFor.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.tbFeedbackFor.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select

tbFeedbackFor.MaxLength = 7

End Sub

Private Sub SubmitFeedback()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table
Set rs = New ADODB.Recordset
rs.Open "FeedbackLog", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs

.AddNew ' create a new record

' add values to each field in the record+
.Fields("User") = tbUser.Value
.Fields("UserFeedback") = tbFeedbackFor.Value
.Fields("Manager") = cbManager.Value
.Fields("DateKeyed") = tbDate.Value
.Fields("Process") = cbProcess.Value
.Fields("ErrorType") = cbError.Value
.Fields("AddDetails") = tbAdditionalDetails.Value
.Fields("Breach") = tbCB.Value
.Fields("NextSteps") = cbNextSteps.Value
.Fields("Notes") = tbNextStepsNotes.Value
.Fields("ProductNum") = tbProductNum.Value
.Fields("FeedbackFrom") = tbfeedbackfrom.Value



If tbCB.Value <> "" Then
.Fields("Breach") = tbCB.Value
End If


' save the new record
.Update

'closing database connection
rs.Close
cn.Close
Set cn = Nothing

If cbNextSteps.Value = "Yes" Then
Call SendConsultantEmail
End If

'confirm to user that task complete
Response = MsgBox("Feedback Submitted, Many Thanks", vbOKOnly, "Switching Department")

End With

'reset to defaults

tbUser.Value = GetUserName()
tbFeedbackFor.Value = ""
DateTo = Date
DateToday = Format$(DateTo, JetDateFormat)
tbDate.Value = DateToday
cbProcess.Value = ""
cbManager.Value = ""
cbError.Value = ""
CheckBox1.Value = False
tbAdditionalDetails.Value = ""
tbCB.Value = ""
tbPersonnel.Value = ""
tbDateFrom.Value = ""
tbDateTo.Value = ""
tbTeam.Value = ""
tbTDateFrom.Value = ""
tbTDateTo.Value = ""
tbItemsOfFeedback.Value = ""
tbNoOfBreaches.Value = ""
tbIncorrectToddasoDetailsEntered.Value = ""
tbIncorrectLetterTypeRequested.Value = ""
tbDidNotFullyCheckApplication.Value = ""
tbIncorrectVariableEntered.Value = ""
tbDiarisedIncorrectly.Value = ""
tbDidNotRequestAllForms.Value = ""
tbNotEndedOnAWD.Value = ""
tbCreditsNotActioned.Value = ""
tbDidNotFullyCheckCustomerNotes.Value = ""
tbFilesNotSplit.Value = ""
tbISAActionedAsSavings.Value = ""
tbDidNotAddNotesCorrectly.Value = ""
tbNotEnteredOnToddaso.Value = ""
tbDidNotCheckIndicators.Value = ""
tbDidNotActionAsManual.Value = ""
tbDidNotLogMobileNumber.Value = ""
tbErrorNotOrderedTwice.Value = ""
tbRejectedIndexing.Value = ""
tbIncorrectBrandOfLetterUsed.Value = ""
tbIncorrectActionTakenOnCase.Value = ""
tbIncorrectLetterUsed.Value = ""
tbIncorrectDiaryDateUsed.Value = ""
tbPPListMissing.Value = ""
tbLetterMissing.Value = ""
tbOther.Value = ""
cbNextSteps.Value = ""
tbNextStepsNotes.Value = ""
tbProductNum.Value = ""
tbfeedbackfrom.Value = ""
tbInitials.Value = ""
tbTeamS.Value = ""
tbPerS.Value = ""

End Sub
'Consultant'
Private Sub ReportMI()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsoverall As ADODB.Recordset

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


DateFrom = Format$(tbDateFrom.Value, JetDateFmt)
DateTo = Format$(tbDateTo.Value, JetDateFmt)
Pers = tbPersonnel.Value

'Set Searchstring for Breach
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [Breach] = ""Yes"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalBreach = rs.RecordCount
tbNoOfBreaches.Value = totalBreach
Set rs = Nothing

'Set Searchstring for Overall Feedback
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbItemsOfFeedback.Value = totalFeedback
Set rs = Nothing

'Set Searchstring for Error(tbIncorrectToddasoDetailEntered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Toddaso details entered"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectToddasoDetailsEntered.Value = "0"
Else
total1 = rs.RecordCount
ConversionPercent = (total1 / totalFeedback) * 100
tbIncorrectToddasoDetailsEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterTypeRequested)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Letter Type Requested"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterTypeRequested.Value = "0"
Else
total2 = rs.RecordCount
ConversionPercent = (total2 / totalFeedback) * 100
tbIncorrectLetterTypeRequested.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckApplication)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not fully check application"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckApplication.Value = "0"
Else
total3 = rs.RecordCount
ConversionPercent = (total3 / totalFeedback) * 100
tbDidNotFullyCheckApplication.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectVariableEntered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Variable Entered"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectVariableEntered.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbIncorrectVariableEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DiarisedIncorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Diarised Incorrectly"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDiarisedIncorrectly.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDiarisedIncorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotRequestAllForms)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not request all forms"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotRequestAllForms.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotRequestAllForms.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEndedOnAWD)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Not ended on AWD"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEndedOnAWD.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbNotEndedOnAWD.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(CreditsNotActioned)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Credits not actioned"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbCreditsNotActioned.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbCreditsNotActioned.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckCustomerNotes)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not fully check customer note"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckCustomerNotes.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotFullyCheckCustomerNotes.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(FilesNotSplit)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Files not Splitt"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbFilesNotSplit.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbFilesNotSplit.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(ISAActionedAsSavings)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""ISA actioned as savings"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbISAActionedAsSavings.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbISAActionedAsSavings.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotAddNotesCorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not add notes correctly"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotAddNotesCorrectly.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotAddNotesCorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEnteredOnToddaso)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Not entered on Toddaso"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEnteredOnToddaso.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbNotEnteredOnToddaso.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotCheckIndicators)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not check indicators"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotCheckIndicators.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotCheckIndicators.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotActionAsManual)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not action as Manual"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotActionAsManual.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotActionAsManual.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotLogMobileNumber)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not log Mobile number"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotLogMobileNumber.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotLogMobileNumber.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If


'Set Searchstring for Error(ErrorNotOrderedTwice)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""09013* not ordered twice"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbErrorNotOrderedTwice.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbErrorNotOrderedTwice.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(RejectedIndexing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Rejected-Indexing"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbRejectedIndexing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbRejectedIndexing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectBrandOfLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Brand of Letter used"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectBrandOfLetterUsed.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectBrandOfLetterUsed.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectActionTakenOnCase)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Action taken on case"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectActionTakenOnCase.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectActionTakenOnCase.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Letter used"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterUsed.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectLetterUsed.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectDiaryDateUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Diary Date Used"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectDiaryDateUsed.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectDiaryDateUsed.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(PPListMissing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""PP List Missing"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbPPListMissing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbPPListMissing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(LetterMissing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Letter Missing"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbLetterMissing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbLetterMissing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(Other)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Other"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbOther.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbOther.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'confirm to user that task complete
Response = MsgBox("MI Collated, Many Thanks", vbOKOnly, "Switching Department")


End Sub
'Consultant'
Private Sub ReportMI2()


Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsoverall As ADODB.Recordset

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


Pers = tbPersonnel.Value

'Set Searchstring for Breach
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [Breach] = ""Yes"""
rs.Open searchstring, cn, adOpenStatic
totalBreach = rs.RecordCount
tbNoOfBreaches.Value = totalBreach
Set rs = Nothing

'Set Searchstring for Overall Feedback
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & ""
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbItemsOfFeedback.Value = totalFeedback
Set rs = Nothing

'Set Searchstring for Error(IncorrectToddasoDetailsEntered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Toddaso details entered"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectToddasoDetailsEntered.Value = "0"
Else
total1 = rs.RecordCount
ConversionPercent = (total1 / totalFeedback) * 100
tbIncorrectToddasoDetailsEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterTypeRequested)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Letter Type Requested"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterTypeRequested.Value = "0"
Else
total2 = rs.RecordCount
ConversionPercent = (total2 / totalFeedback) * 100
tbIncorrectLetterTypeRequested.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckApplication)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not fully check application"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckApplication.Value = "0"
Else
total3 = rs.RecordCount
ConversionPercent = (total3 / totalFeedback) * 100
tbDidNotFullyCheckApplication.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(Incorrect Variable Entered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Variable Entered"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectVariableEntered.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbIncorrectVariableEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(Diarised Incorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Diarised Incorrectly"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDiarisedIncorrectly.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDiarisedIncorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotRequestAllForms)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not request all forms"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotRequestAllForms.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotRequestAllForms.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEndedOnAWD)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Not ended on AWD"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEndedOnAWD.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbNotEndedOnAWD.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(CreditsNotActioned)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Credits not actioned"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbCreditsNotActioned.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbCreditsNotActioned.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckCustomerNotes)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not fully check customer notes"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckCustomerNotes.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotFullyCheckCustomerNotes.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(FilesNotSplit)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Files not Split"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbFilesNotSplit.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbFilesNotSplit.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(ISAActionedAsSavings)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""ISA actioned as savings"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbISAActionedAsSavings.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbISAActionedAsSavings.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotAddNotesCorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not add notes correctly"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotAddNotesCorrectly.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotAddNotesCorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEnteredOnToddaso)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Not entered on Toddaso"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEnteredOnToddaso.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbNotEnteredOnToddaso.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotCheckIndicators)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not check indicators"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotCheckIndicators.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotCheckIndicators.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotActionAsManual)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not action as Manual"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotActionAsManual.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotActionAsManual.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotLogMobileNumber)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Did not log Mobile number"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotLogMobileNumber.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbDidNotLogMobileNumber.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(ErrorNotOrderedTwice)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""09013* not ordered twice"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbErrorNotOrderedTwice.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbErrorNotOrderedTwice.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(RejectedIndexing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Rejected-Indexing"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbRejectedIndexing.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbRejectedIndexing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectBrandOfLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Brand of Letter used"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectBrandOfLetterUsed.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbIncorrectBrandOfLetterUsedd.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectActionTakenOnCase)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Action taken on case"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectActionTakenOnCase.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbIncorrectActionTakenOnCase.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Incorrect Letter used"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterUsedt.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectLetterUsed.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(PPListMissing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""PP List Missing"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbPPListMissing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbPPListMissing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(LetterMissing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Letter Missing"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbLetterMissing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbLetterMissing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If


'Set Searchstring for Error(Other)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [ErrorType] = ""Other"" "
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbOther.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbOther.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'confirm to user that task complete
Response = MsgBox("MI Collated, Many Thanks", vbOKOnly, "Switching Department")

End Sub

'Consultant'

Private Sub GetFullReport()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim DataSheet As Worksheet

Set DataSheet = Workbooks("Switching Feedback.xls").Worksheets("GetReport")

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


DateTo = tbDateTo.Value
DateFrom = tbDateFrom.Value
DateTo = Format$(DateTo, JetDateTimeFmt)
DateFrom = Format$(DateFrom, JetDateTimeFmt)

Pers = tbPersonnel.Value

'Selected Demand type section

Set rs = New ADODB.Recordset

searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & " AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo

rs.Open searchstring, cn, adOpenStatic
sRow = 2

If rs.RecordCount = 0 Then

tbDateFrom.Value = ""
tbDateTo.Value = ""
tbPersonnel.Value = ""

Exit Sub

End If

Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
Range("A2:L65000").Select
Selection.ClearContents
Range("A1").Select

Do

Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
DataSheet.Cells(sRow, "A").Value = rs![ID]
DataSheet.Cells(sRow, "B").Value = rs![User]
DataSheet.Cells(sRow, "C").Value = rs![UserFeedback]
DataSheet.Cells(sRow, "D").Value = rs![Manager]
DataSheet.Cells(sRow, "E").Value = rs![DateKeyed]
DataSheet.Cells(sRow, "F").Value = rs![Process]
DataSheet.Cells(sRow, "G").Value = rs![ErrorType]
DataSheet.Cells(sRow, "H").Value = rs![AddDetails]
DataSheet.Cells(sRow, "I").Value = rs![Breach]
DataSheet.Cells(sRow, "J").Value = rs![NextSteps]
DataSheet.Cells(sRow, "K").Value = rs![Notes]
DataSheet.Cells(sRow, "L").Value = rs![ProductNum]
DataSheet.Cells(sRow, "M").Value = rs![FeedbackFrom]

sRow = sRow + 1
rs.MoveNext

Loop Until rs.EOF

Workbooks("Switching Feedback.xls").Sheets("GetReport").Select
Application.WindowState = xlMaximized


Sheets("Pivot table").Select
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub
'Consultant'
Private Sub GetFullReport2()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim DataSheet As Worksheet

Set DataSheet = Workbooks("Switching Feedback.xls").Worksheets("GetReport")

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


DateTo = tbDateFrom.Value
DateFrom = tbDateTo.Value
Pers = tbPersonnel.Value
DateTo = Format$(DateTo, JetDateTimeFmt)
DateFrom = Format$(DateFrom, JetDateTimeFmt)

'Selected Demand type section

Set rs = New ADODB.Recordset

searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & ""

rs.Open searchstring, cn, adOpenStatic
sRow = 2

If rs.RecordCount = 0 Then

tbDateFrom.Value = ""
tbDateTo.Value = ""
tbPersonnel.Value = ""

Exit Sub

End If

Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
Range("A2:L65000").Select
Selection.ClearContents
Range("A1").Select

Do

Workbooks("Switching Feedback.xls").Sheets("GetReport").Activate
DataSheet.Cells(sRow, "A").Value = rs![ID]
DataSheet.Cells(sRow, "B").Value = rs![User]
DataSheet.Cells(sRow, "C").Value = rs![UserFeedback]
DataSheet.Cells(sRow, "D").Value = rs![Manager]
DataSheet.Cells(sRow, "E").Value = rs![DateKeyed]
DataSheet.Cells(sRow, "F").Value = rs![Process]
DataSheet.Cells(sRow, "G").Value = rs![ErrorType]
DataSheet.Cells(sRow, "H").Value = rs![AddDetails]
DataSheet.Cells(sRow, "I").Value = rs![Breach]
DataSheet.Cells(sRow, "J").Value = rs![NextSteps]
DataSheet.Cells(sRow, "K").Value = rs![Notes]
DataSheet.Cells(sRow, "L").Value = rs![ProductNum]
DataSheet.Cells(sRow, "M").Value = rs![FeedbackFrom]

sRow = sRow + 1
rs.MoveNext

Loop Until rs.EOF

Workbooks("Switching Feedback.xls").Sheets("GetReport").Select
Application.WindowState = xlMaximized



End Sub




End Sub

'Manager'
Private Sub ReportMIM()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsoverall As ADODB.Recordset

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\c900000fsl0001\public$\Heritage HBOS - Banking & Savings Operations\Banking Operations\E&NI Banking\Mike Haydock\PSS\Switching Feedback\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


DateFrom = Format$(tbTDateFrom.Value, JetDateFmt)
DateTo = Format$(tbTDateTo.Value, JetDateFmt)
Pers = tbTeam.Value

'Set Searchstring for Breach
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [Breach] = ""Yes"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalBreach = rs.RecordCount
tbNoOfBreaches.Value = totalBreach
Set rs = Nothing

'Set Searchstring for Overall Feedback
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbItemsOfFeedback.Value = totalFeedback
Set rs = Nothing

'Set Searchstring for Error(tbIncorrectToddasoDetailEntered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Toddaso details entered"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectToddasoDetailsEntered.Value = "0"
Else
total1 = rs.RecordCount
ConversionPercent = (total1 / totalFeedback) * 100
tbIncorrectToddasoDetailsEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterTypeRequested)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Letter Type Requested"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterTypeRequested.Value = "0"
Else
total2 = rs.RecordCount
ConversionPercent = (total2 / totalFeedback) * 100
tbIncorrectLetterTypeRequested.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckApplication)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not fully check application"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckApplication.Value = "0"
Else
total3 = rs.RecordCount
ConversionPercent = (total3 / totalFeedback) * 100
tbDidNotFullyCheckApplication.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectVariableEntered)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Variable Entered"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectVariableEntered.Value = "0"
Else
total4 = rs.RecordCount
ConversionPercent = (total4 / totalFeedback) * 100
tbIncorrectVariableEntered.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DiarisedIncorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Diarised Incorrectly"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDiarisedIncorrectly.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDiarisedIncorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotRequestAllForms)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not request all forms"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotRequestAllForms.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotRequestAllForms.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEndedOnAWD)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Not ended on AWD"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEndedOnAWD.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbNotEndedOnAWD.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(CreditsNotActioned)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Credits not actioned"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbCreditsNotActioned.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbCreditsNotActioned.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotFullyCheckCustomerNotes)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not fully check customer note"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotFullyCheckCustomerNotes.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotFullyCheckCustomerNotes.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(FilesNotSplit)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Files not Splitt"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbFilesNotSplit.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbFilesNotSplit.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(ISAActionedAsSavings)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""ISA actioned as savings"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbISAActionedAsSavings.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbISAActionedAsSavings.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotAddNotesCorrectly)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not add notes correctly"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotAddNotesCorrectly.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotAddNotesCorrectly.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(NotEnteredOnToddaso)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Not entered on Toddaso"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbNotEnteredOnToddaso.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbNotEnteredOnToddaso.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotCheckIndicators)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not check indicators"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotCheckIndicators.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotCheckIndicators.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotActionAsManual)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not action as Manual"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotActionAsManual.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotActionAsManual.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(DidNotLogMobileNumber)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Did not log Mobile number"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbDidNotLogMobileNumber.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbDidNotLogMobileNumber.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If


'Set Searchstring for Error(ErrorNotOrderedTwice)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""09013* not ordered twice"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbErrorNotOrderedTwice.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbErrorNotOrderedTwice.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(RejectedIndexing)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Rejected-Indexing"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbRejectedIndexing.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbRejectedIndexing.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectBrandOfLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Brand of Letter used"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectBrandOfLetterUsed.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectBrandOfLetterUsed.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectActionTakenOnCase)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Action taken on case"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectActionTakenOnCase.Value = "0"
Else
total5 = rs.RecordCount
ConversionPercent = (total5 / totalFeedback) * 100
tbIncorrectActionTakenOnCase.Value = Round(ConversionPercent, 2) & "%"
Set rs = Nothing
End If

'Set Searchstring for Error(IncorrectLetterUsed)
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [ErrorType] = ""Incorrect Letter used"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
If rs.RecordCount = 0 Then
tbIncorrectLetterUsed.Value = "0"
Else
total5 = rs.RecordCount
ConversionPerce

 

[SkipVought]

I am not going to slog thru that mass of code. That is ridiculous!

I just wanted the SQL code that you TRIED to use to use the manager name as a criteria>

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Sorry, about that.

This is an excel userform which operates as the front end of a database so i havent used any SQL code

 

[SkipVought]

THIS is SQL code that you previously posted!!!

searchstring = "SELECT * FROM FeedbackLog WHERE [UserFeedback] = " & Pers & "  AND [ErrorType] = ""ISA actioned as savings"" "

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

I used the same code you have just posted, the difference was the options i added to the combo box

 

[SkipVought]

Where, in your SQL code, did you specify the manager name field and criteria???

If you cannot answer this simple question, you are WAY over you head and need professional help at your desk.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Pers = cbManager.Value

'Set Searchstring for Breach
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & " AND [Breach] = ""Yes"""
rs.Open searchstring, cn, adOpenStatic
totalBreach = rs.RecordCount
tbNoOfBreaches.Value = totalBreach
Set rs = Nothing

'Set Searchstring for Overall Feedback
Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = " & Pers & ""
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbItemsOfFeedback.Value = totalFeedback
Set rs = Nothing

 

[SkipVought]

How can [Manager] in your table have BOTH numeric values and NAMES???

Don't you have something like

1) a [ManagerID] field that might be a NUMBER and

2) a [ManagerName] field that would be TEXT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

No i didn't explain that right, i initially tried using that code with names while [Manager] contained text, when the report did not work i changed the input code to log numeric values under [Manager]

 

[SkipVought]

You must be more precise with your statements!

If your [Manager] field contains text, then the criteria must be delimited by APOSTROPHIES...

searchstring = "SELECT * FROM FeedbackLog WHERE [Manager] = '" & Pers & "' AND [Breach] = '"Yes"'"

where Pers contains a STRING.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Thanks, before i edit the code, should this change alone make the report work with text entries?

 

[SkipVought]

yes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Thanks alot, i have made the changes and it works perfectly, i really appreciate your help with this