Private Sub cmdSelectDate_Click() Dim pMxDoc As IMxDocument Dim pMap As IMap Dim UserInput1 As String Dim i As Integer Dim query1 As String Dim FeatLayer As IFeatureLayer Dim pActiveView As IActiveView Set pMxDoc = ThisDocument Set pActiveView = pMxDoc.ActiveView UserInput1 = cboSelectDate.Text If TypeOf pActiveView Is IPageLayout Then Set pMxDoc.ActiveView = pMxDoc.FocusMap pMxDoc.ActiveView.Refresh End If Set pMap = pMxDoc.FocusMap 'Define/get the table Dim pTabCollection As IStandaloneTableCollection Dim pStTable As IStandaloneTable Dim pSRTable As ITable Set pTabCollection = pMap Dim pTableSel As ITableSelection Dim pTableSelSet As ISelectionSet 'Get the table For i = 0 To pTabCollection.StandaloneTableCount - 1 If pTabCollection.StandaloneTable(i).Name = "Service_requests" Then Set pStTable = pTabCollection.StandaloneTable(i) Set pSRTable = pStTable.Table 'make the query filter query1 = "RequestDate = '" & UserInput1 & Format(RequestDate, "MM/DD/YYYY") & "'" 'doesn't work 'query1 = "RequestDate = #02-05-2011 00:00:00# " , this hardcoded works 'date in table and the combo box looks like MM/DD/YYYY Dim qFil As IQueryFilter Set qFil = New QueryFilter qFil.WhereClause = query1 ' MsgBox (query1) Set pTableSel = pStTable '===select the table rows that meets the query and put it in a selection set pTableSel.SelectRows qFil, esriSelectionResultNew, False Set pTableSelSet = pTableSel.SelectionSet MsgBox pTableSel.SelectionSet.Count '===refresh the view pMxDoc.ActiveView.Refresh End If Next i frmSelectDate.Hide End Sub
Solved! Go to Solution.
query1 = "RequestDate = #" & UserInput1 & Format(RequestDate, "MM/DD/YYYY") & "#" 'mdb needs # delimited
query1 = "Month([Maint_Date1]) = " & UserInput1 And "Year([Maint_Date1]) = " & UserInput2
'query1 = "YEAR([Maint_Date1]) = " & UserInput2 'query1 = "MONTH([Maint_Date1]) = " & UserInput1
Private Sub UserForm_Initialize() Dim pDoc As IMxDocument Dim pMap As IMap Dim pActiveView As IActiveView Dim pLayer As IFeatureLayer Dim pFeatureClass As IFeatureClass Dim i As Integer Dim pTable As ITable Dim pTableSort As ITableSort Dim sFieldName As String Dim pCur As ICursor Dim pRow As IRow Set pDoc = ThisDocument Set pMap = pDoc.FocusMap frmSelectByMonth.cboMonth.Clear 'Define/get the table Dim pTabCollection As IStandaloneTableCollection Dim pStTable As IStandaloneTable Dim pSRTable As ITable Set pTabCollection = pMap 'Get the table For i = 0 To pTabCollection.StandaloneTableCount - 1 If pTabCollection.StandaloneTable(i).Name = "OperationMaint" Then Set pStTable = pTabCollection.StandaloneTable(i) End If Next i Set pSRTable = pStTable.Table 'This example sorts the Maint_Date1 field. sFieldName = "Maint_Date1" Set pTableSort = New TableSort With pTableSort .Fields = sFieldName .Ascending(sFieldName) = True Set .Table = pSRTable End With 'Sort the table pTableSort.Sort Nothing 'loop through the sorted records and add 'to a list box Set pCur = pTableSort.Rows Dim pData As IDataStatistics Set pData = New esriGeoDatabase.DataStatistics pData.Field = "Maint_Date1" Set pData.Cursor = pCur 'use the cursor created from TableSort Dim pEnumVar As IEnumVariantSimple, value As Variant, str As String Set pEnumVar = pData.UniqueValues value = pEnumVar.Next Do Until IsEmpty(value) frmSelectByMonth.cboMonth.AddItem value value = pEnumVar.Next Loop 'cboMonth.AddItem "2" 'cboYear.AddItem "2011" End Sub
query1 = "Month([Maint_Date1]) = " & UserInput1 & " And Year([Maint_Date1]) = " & UserInput2
Private Sub cmdSelectByMonth_Click() Dim pMxDoc As IMxDocument Dim pMap As IMap Dim UserInput1 As String Dim UserInput2 As String Dim i As Integer Dim query1 As String Dim FeatLayer As IFeatureLayer Dim pActiveView As IActiveView Set pMxDoc = ThisDocument Set pActiveView = pMxDoc.ActiveView UserInput1 = cboMonth.Text UserInput2 = cboYear.Text If TypeOf pActiveView Is IPageLayout Then Set pMxDoc.ActiveView = pMxDoc.FocusMap pMxDoc.ActiveView.Refresh End If Set pMap = pMxDoc.FocusMap 'Define/get the table Dim pTabCollection As IStandaloneTableCollection Dim pStTable As IStandaloneTable Dim pSRTable As ITable Set pTabCollection = pMap Dim pTableSel As ITableSelection Dim pTableSelSet As ISelectionSet 'Get the table For i = 0 To pTabCollection.StandaloneTableCount - 1 If pTabCollection.StandaloneTable(i).Name = "OperationMaint" Then Set pStTable = pTabCollection.StandaloneTable(i) Set pSRTable = pStTable.Table 'make the query filter where clause query1 = "Month([Maint_Date1]) = " & UserInput1 & " And Year([Maint_Date1]) = " & UserInput2 Dim qFil As IQueryFilter Set qFil = New QueryFilter qFil.WhereClause = query1 Set pTableSel = pStTable '===select the table rows that meets the query and put it in a selection set pTableSel.SelectRows qFil, esriSelectionResultNew, False Set pTableSelSet = pTableSel.SelectionSet 'tells user how many records were selected MsgBox "You have selected " & pTableSel.SelectionSet.Count & " records" 'need msgbox that tells sum of the PipeFeet double field 'find field in table, sum the field from statistics, put the sum in variable, msgbox variable '===refresh the view pMxDoc.ActiveView.Refresh End If Next i frmSelectByMonth.Hide End Sub
Private Sub cmdSelectByMonth_Click() Dim pMxDoc As IMxDocument Dim pMap As IMap Dim UserInput1 As String Dim UserInput2 As String Dim i As Integer Dim query1 As String Dim FeatLayer As IFeatureLayer Dim pActiveView As IActiveView Set pMxDoc = ThisDocument Set pActiveView = pMxDoc.ActiveView UserInput1 = cboMonth.Text UserInput2 = cboYear.Text If TypeOf pActiveView Is IPageLayout Then Set pMxDoc.ActiveView = pMxDoc.FocusMap pMxDoc.ActiveView.Refresh End If Set pMap = pMxDoc.FocusMap 'Define/get the table Dim pTabCollection As IStandaloneTableCollection Dim pStTable As IStandaloneTable Dim pSRTable As ITable Set pTabCollection = pMap Dim pTableSel As ITableSelection Dim pTableSelSet As ISelectionSet 'Get the table For i = 0 To pTabCollection.StandaloneTableCount - 1 If pTabCollection.StandaloneTable(i).Name = "OperationMaint" Then Set pStTable = pTabCollection.StandaloneTable(i) Set pSRTable = pStTable.Table 'make the query filter where clause query1 = "Month([Maint_Date1]) = " & UserInput1 & " And Year([Maint_Date1]) = " & UserInput2 Dim qFil As IQueryFilter Set qFil = New QueryFilter qFil.WhereClause = query1 Set pTableSel = pStTable '===select the table rows that meets the query and put it in a selection set pTableSel.SelectRows qFil, esriSelectionResultNew, False Set pTableSelSet = pTableSel.SelectionSet 'This creates a msgbox that tells sum of the PipeFeet double field and how many records were selected Dim pCursor As ICursor Dim pRow As IRow Dim pData As IDataStatistics Dim pStatResults As IStatisticsResults Set pCursor = pSRTable.Search(qFil, False) Set pData = New DataStatistics pData.Field = "PipeFeet" Set pData.Cursor = pCursor Set pStatResults = pData.Statistics MsgBox "You have selected " & pTableSel.SelectionSet.Count & " records" & vbCrLf & vbCrLf & _ "There was " & pStatResults.Sum & " feet of pipe cleaned this month", vbInformation '===refresh the view pMxDoc.ActiveView.Refresh End If Next i frmSelectByMonth.Hide End Sub Private Sub UserForm_Initialize() cboMonth.AddItem "1" cboMonth.AddItem "2" cboMonth.AddItem "3" cboMonth.AddItem "4" cboMonth.AddItem "5" cboMonth.AddItem "6" cboMonth.AddItem "7" cboMonth.AddItem "8" cboMonth.AddItem "9" cboMonth.AddItem "10" cboMonth.AddItem "11" cboMonth.AddItem "12" cboYear.AddItem "2008" cboYear.AddItem "2009" cboYear.AddItem "2010" cboYear.AddItem "2011" cboYear.AddItem "2012" cboYear.AddItem "2013" cboYear.AddItem "2014" End Sub