Hi All
Hoping one of the brilliant people on this forum can help me solve this dilemma .
Sheet: MapblasterData
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 82"]
<tbody>[TR]
[TD="width: 82"]PTWREF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 128"]
<tbody>[TR]
[TD="width: 128"]REPORTEDDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]CHG0077755[/TD]
[TD]01-09-14 08:45[/TD]
[/TR]
[TR]
[TD]CHG0077755[/TD]
[TD]30-08-14 12:29[/TD]
[/TR]
[TR]
[TD]CHG0085423[/TD]
[TD]31-08-14 09:00[/TD]
[/TR]
[TR]
[TD]CHG0083824[/TD]
[TD]31-08-14 12:29[/TD]
[/TR]
[TR]
[TD]CHG0077755[/TD]
[TD]01-09-14 15:20[/TD]
[/TR]
[TR]
[TD]CHG0077755[/TD]
[TD]01-09-14 10:11[/TD]
[/TR]
[TR]
[TD]CHG0082227[/TD]
[TD]30-08-14 16:03[/TD]
[/TR]
</tbody>[/TABLE]
1. I have a combobox to load PTWREF values. On change event, I would like to see total number of reports against selected PTWREF with daily breakdown (I do not want to consider Time).
e.g. On Selecting "CHG0077755" in the combobox, Output should look like
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Number of Reports[/TD]
[/TR]
[TR]
[TD]30-08-14[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01-09-14[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
Also I have a cmdbutton to create corresponding 2D chart with Date on H-Axis and Number of Reports on Y-axis (whole number (1,2,3, etc) only on Y-Axis).
Code:
Dim intMaxRows As Integer
Private Sub CmbPTWList_Change()
Dim RowC As Integer
'working query
strSQL = "SELECT [MapblasterData$].[REPORTEDDATE],Count([MapblasterData$].[REPORTEDDATE]) FROM [MapblasterData$] WHERE ([MapblasterData$].[PTWREF]) = '" & CmbPTWList.Text & "' GROUP BY [MAPBLASTERDATA$].[REPORTEDDATE],[MAPBLASTERDATA$].[PTWREF] ;"
closeRS
OpenDB
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Sheets("PTWRaised").Visible = True
Sheets("PTWRaised").Select
'Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
Range("B6:C50").Clear
'Now putting the data on the sheet
'ActiveCell.CopyFromRecordset rs
Range("B6").CopyFromRecordset rs
Else
MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
closeRS
Exit Sub
End If
intMaxRows = rs.RecordCount ' to use for specifying range for charts
Set cnn = Nothing
Set rs = Nothing ' dispose the recordset
Sheets("PTWRaised").Columns("C").Cells.HorizontalAlignment = xlCenter
Dim StrSiteName As String
' vlookup for Site Name in another sheet 'SM9 Export' retrieved into a multipline textbox
Set ShtMapblasterData = ActiveWorkbook.Sheets("MapblasterData")
Set ShtSM9Export = ActiveWorkbook.Sheets("SM9 Export")
StrSiteName = Application.WorksheetFunction.VLookup(CmbPTWList.Text, ShtSM9Export.Range("A1:B10000"), 2, False)
TextBox1.MultiLine = True
TextBox1.AutoSize = False
TextBox1.Value = StrSiteName
CmdCreateChart_Click ' calling create chart event
End Sub
------------------------------------------------------------------------------------------------------------------------------
Private Sub CmdCreateChart_Click()
Range("B5:C" & (5 + intMaxRows) & " ").Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="PTWRaised"
End SubProblem with Above code:
1. It is taking into account time in REPORTEDDATE field as well and hence showing 3 different rows for 01-09-2014 as I need daily reports only. Currently date column in the output is showing date in dd/mm/yyyy format but formula bar shows dd/mm/yyyy hh:mm:ss.
2. some rows are changing date format to mm/dd/yyyy by itself. I have column formatting set to dd/mm/yyyy even.
Please help Thanks
