By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Sort Report on Fly between dates

P: 147
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.
Expand|Select|Wrap|Line Numbers
  1. ElseIf TypeOf c Is Access.TextBox Then
  2. strSQL = strSQL & "([" & c.Tag & "] BETWEEN  #" & c & "# And Forms![frmParkingFilter]![EndingDate])  And "
Feb 18 '09 #1
Share this Question
Share on Google+
7 Replies

P: 147
Opps that should only have two lines sorry about that I am trying to learn to use the site properly. Thanks
Feb 18 '09 #2

Expert Mod 15k+
P: 31,419
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.
Feb 18 '09 #3

P: 147
Thanks but I got it it goes like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub FilterParking_Click()
  2.   Dim strSQL As String, intCounter As Integer
  3.   Dim c As Access.Control
  4.   Dim stFilter As String
  5.   stFilter = ("DateReceived Is Null")
  6.   For intCounter = 1 To 3
  7.     If Me("Filter" & intCounter) <> "" Then
  8.     Set c = Me("Filter" & intCounter)
  9.       If TypeOf c Is Access.ComboBox Then
  10.         strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
  11.       ElseIf TypeOf c Is Access.TextBox Then
  12.         strSQL = strSQL & "([" & c.Tag & "] BETWEEN  #" & c & "# And #" & [EndingDate] & "#) And "
  13.       ElseIf TypeOf c Is Access.CheckBox Then
  14.         If c.Value = True Then
  15.           strSQL = strSQL & stFilter & " And "
  16.         End If
  17.       End If
  18.     End If
  19.   Next
  20.   If strSQL <> "" Then
  21.     strSQL = Left(strSQL, (Len(strSQL) - 5))
  22.     Reports!rptParkingTickets.Filter = strSQL
  23.     Reports!rptParkingTickets.FilterOn = True
  24.   End If
Feb 18 '09 #4

Expert Mod 15k+
P: 31,419
I guess this means you were never talking about sorting the report at all, but rather about applying a filter?
Feb 18 '09 #5

Expert Mod 2.5K+
P: 2,545
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.
Feb 18 '09 #6

P: 147
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.
Feb 20 '09 #7

Expert Mod 15k+
P: 31,419
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.
Feb 20 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.