How do you search a date field in a file GDB feature class?

PatriciaPendleton · ‎08-30-2010

Hi all,
I am trying to set up an input box so users can select points by a date field. The user types in a "Date From" and a "Date To", and the selection is supposed to occur. However, as the code is, no selection occurs when the input boxes are used. If I were to do the selection manually with the Select by Attribute tool, the dates show up in the format of " date 'YYYY/MM/DD' ", and if I type dates in that format directly into the code, it makes the correct selections--but I get a type mismatch error if that's typed into the user boxes. Where am I going wrong?

Ideally, I'd like the user to be able to type the date in the format of just MM/DD/YYYY, as that is how the dates are displayed in the attribute table, but whatever will work will do.

Thanks in advance for any help!

Public Sub GetDates()

Dim pDoc As IMxDocument
Dim pMap As IMap
Dim pActiveView As IActiveView
Dim pFeatureLayer As IFeatureLayer
Set pDoc = ThisDocument
Set pMap = pDoc.FocusMap
Set pActiveView = pMap

Dim pQueryFilter As IQueryFilter
    Dim pFeatureSelection As IFeatureSelection

Dim DateFrom As Date
DateFrom = datDateFrom.Text
Dim DateTo As Date
DateTo = datDateTo.Text

'Other alternatives I can think of also don't work, such as:
'Dim DateFrom As String '(or Date)
'DateFrom = "date '" & datDateFrom.Text & "''
'Dim DateTo As String '(or Date)
'DateTo = "date '" & datDateTo.Text &"'"     

    Application.Document.CommandBars.Find(ArcID.Query_ClearSelection).Execute
    Set pFeatureLayer = pMap.Layer(0)
    'Prepare a query filter
    Set pQueryFilter = New QueryFilter
    pQueryFilter.WhereClause = "OccurredDate1 >= DateFrom And OccurredDate1 <= DateTo"
    'Refresh the old selection, if any, and erase it
    pActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing
    'Select features
    Set pFeatureSelection = pFeatureLayer
    pFeatureSelection.SelectFeatures pQueryFilter, esriSelectionResultNew, False
    'Refresh again to draw the new selection
    pActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing
    
End Sub

Patricia Pendleton
Center for Geographical Studies
Cal State University, Northridge

LanceShipman · ‎08-30-2010

Define DateFrom and DateTo as string rather than date.

Lance

Hi all,
I am trying to set up an input box so users can select points by a date field. The user types in a "Date From" and a "Date To", and the selection is supposed to occur. However, as the code is, no selection occurs when the input boxes are used. If I were to do the selection manually with the Select by Attribute tool, the dates show up in the format of " date 'YYYY/MM/DD' ", and if I type dates in that format directly into the code, it makes the correct selections--but I get a type mismatch error if that's typed into the user boxes. Where am I going wrong?

Ideally, I'd like the user to be able to type the date in the format of just MM/DD/YYYY, as that is how the dates are displayed in the attribute table, but whatever will work will do.

Thanks in advance for any help!
Public Sub GetDates()

Dim pDoc As IMxDocument
Dim pMap As IMap
Dim pActiveView As IActiveView
Dim pFeatureLayer As IFeatureLayer
Set pDoc = ThisDocument
Set pMap = pDoc.FocusMap
Set pActiveView = pMap

Dim pQueryFilter As IQueryFilter
    Dim pFeatureSelection As IFeatureSelection

Dim DateFrom As Date
DateFrom = datDateFrom.Text
Dim DateTo As Date
DateTo = datDateTo.Text

'Other alternatives I can think of also don't work, such as:
'Dim DateFrom As String '(or Date)
'DateFrom = "date '" & datDateFrom.Text & "''
'Dim DateTo As String '(or Date)
'DateTo = "date '" & datDateTo.Text &"'"     

    Application.Document.CommandBars.Find(ArcID.Query_ClearSelection).Execute
    Set pFeatureLayer = pMap.Layer(0)
    'Prepare a query filter
    Set pQueryFilter = New QueryFilter
    pQueryFilter.WhereClause = "OccurredDate1 >= DateFrom And OccurredDate1 <= DateTo"
    'Refresh the old selection, if any, and erase it
    pActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing
    'Select features
    Set pFeatureSelection = pFeatureLayer
    pFeatureSelection.SelectFeatures pQueryFilter, esriSelectionResultNew, False
    'Refresh again to draw the new selection
    pActiveView.PartialRefresh esriViewGeoSelection, Nothing, Nothing
    
End Sub
Patricia Pendleton
Center for Geographical Studies
Cal State University, Northridge

PatriciaPendleton · ‎08-30-2010

Thanks so much for responding!

...But when I try

Dim DateFrom As String
DateFrom = datDateFrom.Text
Dim DateTo As String
DateTo = datDateTo.Text

with the query filter set to:
pQueryFilter.WhereClause = "OccurredDate1 >= DateFrom And OccurredDate1 <= DateTo"

nothing gets selected. Yet the selection works fine when I type dates directly into the code:
pQueryFilter.WhereClause = "OccurredDate1 >= date '2009/09/11' And OccurredDate1 <= date '2009/09/11'"

I hope you can see why I am confused...

LanceShipman · ‎08-30-2010

pQueryFilter.WhereClause = "OccurredDate1 >= DateFrom And OccurredDate1 <= DateTo"

Should be:

pQueryFilter.WhereClause = "OccurredDate1 >= " & DateFrom & " And OccurredDate1 <= " & DateTo

The variables will not be in the string otherwise. For debugging reasons you may want to print out the where clause string.

Debug.Print pQueryFilter.WhereClause

RichardFairhurst · ‎08-30-2010

Patricia:

Your problem is with the line of code below:

    pQueryFilter.WhereClause = "OccurredDate1 >= DateFrom And OccurredDate1 <= DateTo"

This would be translated as though DateFrom and DateTo are actual fields in your feature class (like OccurredDate1) rather than as date values that the user is supplying. In case Lance's suggested syntax does not work try the clause below to replicate the format you said worked:

pQueryFilter.WhereClause = "OccurredDate1 >= date '" & format(DateFrom, "YYYY/MM/DD") & "' And OccurredDate1 <= date '" &  format(DateTo, "YYYY/MM/DD") & "'"

PatriciaPendleton · ‎08-30-2010

"Ah, I see!" said the blind newbie.
Thanks so much for the explanation, worked like a charm.