Sort Report on Fly between dates

[DAHMB]

I am trying to sort a report on the fly with dates please let me know whow I can get the following code to work, I know I have it configured incorrectly.
Thanks

ElseIf TypeOf c Is Access.TextBox Then
strSQL = strSQL & "([" & c.Tag & "] BETWEEN  #" & c & "# And Forms![frmParkingFilter]![EndingDate])  And "

[DAHMB]

Opps that should only have two lines sorry about that I am trying to learn to use the site properly. Thanks
Dan

[NeoPa]

Dan, there's not even a reference to your report in the code. From the look of it, this is code to build up a SQL string.

Please explain your question more clearly, using names of objects where appropriate. An explanation of what (& why) you are trying to do would help too.

There are various techniques to use, knowing which is appropriate for you, requires some understanding of your situation.

[DAHMB]

Thanks but I got it it goes like this:

Private Sub FilterParking_Click()
  Dim strSQL As String, intCounter As Integer
  Dim c As Access.Control
  Dim stFilter As String
  stFilter = ("DateReceived Is Null")
  For intCounter = 1 To 3
    If Me("Filter" & intCounter) <> "" Then
    Set c = Me("Filter" & intCounter)
      If TypeOf c Is Access.ComboBox Then
        strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
      ElseIf TypeOf c Is Access.TextBox Then
        strSQL = strSQL & "([" & c.Tag & "] BETWEEN  #" & c & "# And #" & [EndingDate] & "#) And "
      ElseIf TypeOf c Is Access.CheckBox Then
        If c.Value = True Then
          strSQL = strSQL & stFilter & " And "
        End If
      End If
    End If
  Next
  If strSQL <> "" Then
    strSQL = Left(strSQL, (Len(strSQL) - 5))
    Reports!rptParkingTickets.Filter = strSQL
    Reports!rptParkingTickets.FilterOn = True
  End If

[NeoPa]

I guess this means you were never talking about sorting the report at all, but rather about applying a filter?

[Stewart Ross]

Hi. Glad you got this sorted out, but as NeoPa pointed out there was a mismatch between what you told us and what you really wanted - this is not about sorting a report on the fly, it is about filtering the report for tickets issued between certain dates. A crucial difference.

It is also simpler in my opinion just to filter the report when you open it from DoCmd.OpenReport. You can supply a WHERE clause (without the WHERE keyword) as one of the parameters to the OpenReport method.

An extract from the MS Help entry for OpenReport is shown below:

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

-Stewart

[DAHMB]

Yes but with the code above I can open the report and then filter it in various ways without closing and reopening it using diferent queries and/or command buttons.

[NeoPa]

There certainly are occasions where you may well want to change the filtering on the fly Dan. It doesn't seem to come up much, but depending on requirements, it can do.

I think Stewart's main point though, was that a fair amount of time could be saved if you went to the trouble of checking your posts a bit more carefully before submitting them.

I don't think that's too unreasonable an expectation in the circumstances.