Access List Form - filter by date - SQL database

Question

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

user3033348 · Accepted Answer · 2022-09-07 20:57:00Z

0

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

answered Sep 7, 2022 at 20:57

user3033348

1431 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2022-09-08 14:46:40Z

0

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") & "#"

answered Sep 8, 2022 at 14:46

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

user3033348 Over a year ago

When you link a SQL database to an Access frontend, date fields show up as text, not date.

Gustav Over a year ago

That will only happen if you use DateTime2 and the old SQL Server ODBC driver. Use DateTime and a current SQL Server driver like version 18.

Collectives™ on Stack Overflow

Access List Form - filter by date - SQL database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related