Filter a list box on a form with date ranges

[Bombshell Disaster]

Hi Guys

Can you help with the criteria for filtering a list box with date ranges on a form.

Form1 has a list box with multiple fields, one of the fields is an invoice date, in Column(2).

Form1 has two unbound text boxes: txtStartDate and txtEndDate. Also one of the options buttons that selects filtering by Date Range optDateRange.

After optDateRange has got focus txtStartDate and txtEndDate will have the default date of today. I then want to filter the list box on invoice date between txtStartDate and txtEndDate. I will also add another AfterUpdate event to the txtEndDate to filter the list box if the txtEndDate changes.

Please can you help with the code for filtering the list box between dates.

Many thanks.

 

[CyberLynx]

I'm assuming the ListBox (ListBox1) in filled with a SELECT query based off the Invoices Table.

Private Sub txtStartDate_AfterUpdate()
   Dim StrgSQL As String
   If Me.optDateRange.Value = True Then
      StrgSQL = "SELECT Field1, Field2, Field3, etc FROM tblInvoices " & _
                "WHERE [InvoiceDate] BETWEEN #" & CDate(Me.txtStartDate) & _
                "# AND #" & CDate(Me.txtEndDate) & "#;"
   Else
      StrgSQL = "SELECT Field1, Field2, Field3, etc FROM tblInvoices " & _
               "WHERE [InvoiceDate]=#" & CDate(Me.txtStartDate) & "#;" 
   End If

   Me.ListBox1.RowSource = StrgSQL
End Sub

Private Sub txtEndDate_AfterUpdate()
   Dim StrgSQL As String
   StrgSQL = "SELECT Field1, Field2, Field3, etc FROM tblInvoices " & _
              "WHERE [InvoiceDate] BETWEEN #" & CDate(Me.txtStartDate) & _
              "# AND #" & CDate(Me.txtEndDate) & "#;"

   Me.ListBox1.RowSource = StrgSQL
End Sub

Of course you will need to ensure that proper Field names are in place.

.

 

[Bombshell Disaster]

Error

Hi, thanks for your reply.

There are default dates of today in the text boxes. The list is not filtering on the default dates and then if I type a date in the boxes it comes up with the following error.

RunTime Error 2427 You entered an expression that has no value, and then checks this line of code:

If Me.optDateRange.Value = True Then

This is the code that is attached:

Private Sub txtEndDate_AfterUpdate()

Dim StrgSQL As String
StrgSQL = "SELECT * FROM qryInvoices " & _
"WHERE [OrderDate] BETWEEN #" & CDate(Me.txtStartDate) & _
"# AND #" & CDate(Me.txtEndDate) & "#;"

Me.InvList.RowSource = StrgSQL

End Sub

Private Sub txtStartDate_AfterUpdate()

Dim StrgSQL As String
If Me.optDateRange.Value = True Then
StrgSQL = "SELECT * FROM qryInvoices " & _
"WHERE [OrderDate] BETWEEN #" & CDate(Me.txtStartDate) & _
"# AND #" & CDate(Me.txtEndDate) & "#;"
Else
StrgSQL = "SELECT * FROM qryInvoices " & _
"WHERE [OrderDate]=#" & CDate(Me.txtStartDate) & "#;"
End If

Me.InvList.RowSource = StrgSQL

End Sub

Thanking you for your help...

 

[Bombshell Disaster]

Found the error

Hi

I have worked out the error that I was getting and changed the line to look for a blank field in the txtEndDate. So many thanks for your original code.

Though have come up with a new question if that is OK? My dates are in English format. Though when I filter on date ranges is is reading the format as US. mm-dd-yyyy.

How can I view dates and type dates in English format, and not filter by month first?

Thanking you again.

 

[Bombshell Disaster]

Finished

Sorry, seem to have answered my questions.

Thanks for your time.

How do you close a thread?

 

[CyberLynx]

Haha...How do you close a thread?

Like this....

THREAD CLOSED

Just kidding

.

 

[Bombshell Disaster]

Thanks awfully... Will no doubt call on your services again...