select by Date personal geodatabase

[Anonymous User]

Original User: alnesbit

Hello all,

I'm trying to populate a combo box with the date values currently in my table within a personal geodatabase (not a feature class, just a table). Then the user will select one of those dates and the records with that date will be selected. Pretty simple. I have the combo box getting filled with the correct values. But I can't get the code to work for the selection and it has to do with the date formatting. Can anyone help?

 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

Thanks in advance,

[NeilClemmons · Accepted solution]

query1 = "RequestDate = '" & UserInput1 & Format(RequestDate, "MM/DD/YYYY") & "'" 'doesn't work

You're delimiting the date value with single quotes.  That's incorrect syntax for the flavor or SQL that Access databases use.

'query1 = "RequestDate = #02-05-2011 00:00:00# " , this hardcoded works

You're delimiting the date with pounds.  This is correct.  So, replace the single quotes in your first line with pounds.

[Anonymous User]

Original User: alnesbit

Neil,

Good to 'see' you again. I know you had helped me many times in the old forums, either directly or indirectly.

Right you are! this works like a charm.

query1 = "RequestDate = #" & UserInput1 & Format(RequestDate, "MM/DD/YYYY") & "#" 'mdb needs # delimited

Thanks again!!

[Anonymous User]

Original User: alnesbit

Neil or anyone else,

Do you know how I could select by month? Could I do something like
RequestDate LIKE # 05 *# ?  Trying to select all the records that start with 05 (so I get all from May). Or even trying to get all from May of 2008, for example?

I don't know if that's possible.

Thanks in advance,

[NeilClemmons]

I haven't tested this yet but I believe Access supports the MONTH, DAY, and YEAR functions.  Your query would look something like this:

query1 = "MONTH([FieldName]) = 4"

[Anonymous User]

Original User: alnesbit

Neil,

Ok, thanks! that works. I am running into a few other questions. Maybe you can help? I'm trying to get the month and the year from the user. So I'm trying this:

    

 query1 = "Month([Maint_Date1]) = " & UserInput1 And "Year([Maint_Date1]) = " & UserInput2
         

It doesn't work. Separately they work, but how do I combine them?

'query1 = "YEAR([Maint_Date1]) = " & UserInput2
            'query1 = "MONTH([Maint_Date1]) = " & UserInput1

Also, is there anyway to load the months and years from the table into a combo box? I have the whole date loading in the combo box like this below. And when I tried to tell it MONTH or YEAR, I couldn't get it to just return those.

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

If you're not sure I can start a new forum question as this is getting a little more involved than my initial question.

Thanks!!

[NeilClemmons]

You're concatenating your query string incorrectly:

query1 = "Month([Maint_Date1]) = " & UserInput1 & " And Year([Maint_Date1]) = " & UserInput2

A good way to debug this sort of thing is to display the string in a message box or print it to the Debug window.  You can also step through the code with the debugger and examine the variable's value after executing that line of code.  This will show you exactly what your query string is.

As for adding the values to the combobox, don't try to have your query return the parsed date values.  Just have it return the whole date then parse the date for the pieces you want using VB's date functions.  I'm pretty sure the names of VB's date functions are MONTH, DAY, and YEAR.

[Anonymous User]

Original User: alnesbit

Neil or anyone else,

Ok, thanks. I tried to do a msgbox on my query1 but it said type mismatch. So I wasn't sure what to do then. I have decided to give up on trying to get the Month and Year from the values in the table to put in my combo box. It was just too complicated so I am just adding them in like "1" then "2" and I'm just adding in 1-12 and then a bunch of years individually too, like "2010" and "2011" etc.

I hate to keep bugging you, but I have one final thing I need to do for this one. I need to pop a message box that states how many feet of pipe are selected. So my code is below. So I need to sum the PipeFeet field from the selected records in my table that the user just selected. I can show the count of features selected really easily. I guess I was just hoping I could do "pTableSel.SelectionSet.PipeFeet.Sum", ha! Do you know how I need to code this?

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

Appreciate any help. Thanks,

[Anonymous User]

Original User: alnesbit

Got it!

Thank you for all of your previous help! Couldn't have done it without you.

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

Cheers,