DATE format

[amsaini15]

Hi All
Hoping one of the brilliant people on this forum can help me solve this dilemma .


Sheet: MapblasterData

PTWREF <tbody> </tbody>	REPORTEDDATE <tbody> </tbody>
CHG0077755	01-09-14 08:45
CHG0077755	30-08-14 12:29
CHG0085423	31-08-14 09:00
CHG0083824	31-08-14 12:29
CHG0077755	01-09-14 15:20
CHG0077755	01-09-14 10:11
CHG0082227	30-08-14 16:03

<tbody>
</tbody>

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

Date	Number of Reports
30-08-14	1
01-09-14	3

<tbody>
</tbody>

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).

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 Sub

Problem 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

 

[amsaini15]

Remove Duplicate from list and sum up

Hi All
After playing around with the code, I have used the DATEVALUE function to extract just the date from the date/time results. Now my outcome looks like:

Reported Date	Number of Reports
22-08-14	1
22-08-14	1
27-08-14	1
27-08-14	3
27-08-14	1
31-08-14	1
31-08-14	7

<tbody>
</tbody>

I need to code to remove duplicate dates and sum up corresponding 'Number of reports". Also would require another row to sum total reports

Output should look like

Reported Date	Number of Reports
22-08-14	2
27-08-14	5
31-08-14	8
Total Reports	15

<tbody>
</tbody>

Thanks in advance for your help.