Access List Form - filter by date - SQL database-CodePudding

SQL database with dates. I have a list form. At the top of the form, I am trying to filter the data by Date Received. When it brings it into Access, DateReceived shows in yyyy-MM-dd format so I have the SQL data invisible and I have a formatted text box (txtDateReceived) which takes the SQL date and formats it into mm/dd/yyyy which is visible. The user will enter the date in the mm/dd/yyyy format OR pick from the calendar. I tried to apply the filter to DateReceived and to txtDateReceived, and can't get either to work. Here is my code (includes everything I tried):

   Private Sub TextDateR_AfterUpdate()
      'declare variables
      Dim sFilter As String
    
      'in this case, the ID is text so the ID value
      'needs to be wrapped in single quotes.
      sFilter = "[DateReceived]= " & Format(Me.TextDateR, "yyyy-MM-dd")
      'sFilter = "DateReceived = #" & Me.TextDateR & "#"
      'sFilter = "txtDateReceived =" & Me.TextDateR
      
      'assign the filter value,and turn filtering on
      Me.Filter = sFilter
      Me.FilterOn = True
      Me.Recalc
      Forms!frmSurplusList.Requery
      Forms!frmSurplusList.Repaint
      
      sFilter = ""
    End Sub

CodePudding user response：

Figured it out! sFilter = "[DateReceived]= '" & Format(Me.TextDateR, "yyyy-MM-dd") & "'"

CodePudding user response：

You should create not a text date (that will be casted) but a date value expression using octothorpes:

sFilter = "[DateReceived] = #" & Format(Me.TextDateR, "yyyy-MM-dd") & "#"