Setting OrderBy Property to DateDiff() on Report

[Greg]

The scenerio is, I have a form that allows users to filter and sort a
report that is open in the background. One of the options is a
calculation between two dates but it isn't working. Instead of sorting
by the DateDiff calculation, it just sorts by the dates. When I go
into the report and look at the properties after the filtering/sorting
has been applied, the OrderBy property has the DateDiff in there
correctly, but it just isn't working. I'm new to VBA, so everything
I've built I've learned to do on the fly. My experience is in VB
Script/ASP. Any help would be appreciated. I can send a copy of the
database if necessary.


here is the abreviated version of the code I'm using:

With Reports![StateReport]
strSortOrder = "DateDiff('d',[Claim_Date_Rcvd],[Claim_Date_Cmpltd])"
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With



Thanks,

Greg

 

[Dirk Goldgar]

The scenerio is, I have a form that allows users to filter and sort a
report that is open in the background. One of the options is a
calculation between two dates but it isn't working. Instead of
sorting by the DateDiff calculation, it just sorts by the dates.
When I go into the report and look at the properties after the
filtering/sorting has been applied, the OrderBy property has the
DateDiff in there correctly, but it just isn't working. I'm new to
VBA, so everything I've built I've learned to do on the fly. My
experience is in VB Script/ASP. Any help would be appreciated. I
can send a copy of the database if necessary.


here is the abreviated version of the code I'm using:

With Reports![StateReport]
strSortOrder = "DateDiff('d',[Claim_Date_Rcvd],[Claim_Date_Cmpltd])"
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With

If your report has any sorting or grouping defined in the Sorting and
Grouping dialog, that will override any setting you may put in the
OrderBy property. Could that be the problem?

 

[Greg]

The report is grouped by State so I didn't think that would affect the
OrderBy. I'm trying to sort the records under each group. Would
grouping by State affect the OrderBy for the DateDiff?

 

[Dirk Goldgar]

The report is grouped by State so I didn't think that would affect the
OrderBy. I'm trying to sort the records under each group. Would
grouping by State affect the OrderBy for the DateDiff?

Yes, it would. Add a second grouping level, underneath State, but don't
use a group header or footer for it. If you only ever want to sort by
the DateDiff expression, you can just set that at design time as the
Field/Expression in the Sorting and Grouping dialog. If you want to
modify it dynamically, you can change it in code, but only in the
report's Open event, not afterward. here's an example:

'----- start of code -----
Private Sub Report_Open(Cancel As Integer)

Dim strSortBy

strSortBy = InputBox("Enter sort field/expression:")

If Len(strSortBy) > 0 Then
Me.GroupLevel(0).ControlSource = strSortBy
End If

' Note: GroupLevel(0) is the first group level. For the second
' group level, you'd use GroupLevel(1), and so on.

End Sub
'----- end of code -----

 

[Greg]

There are 9 different filtering fields on my form and all of them were
designed to function with the report open. All of the other options
for this sorting field work correctly. The DateDiff is the only one
not working. I would have to completely rebuild the entire form to use
OnOpen. There's no other way to do this with the report already open?

 

[Dirk Goldgar]

There are 9 different filtering fields on my form and all of them were
designed to function with the report open. All of the other options
for this sorting field work correctly. The DateDiff is the only one
not working. I would have to completely rebuild the entire form to
use OnOpen. There's no other way to do this with the report already
open?

If the others all work, maybe I'm wrong about this. But if the only
difference between the ones that work and this one that doesn't is that
the others are all just fields in the report's recordsource, not
expressions, you might try adding a calculated field to the report's
recordsource query:

DaysToComplete: DateDiff("d",[Claim_Date_Rcvd],[Claim_Date_Cmpltd])

Then, with that field defined in the query, see if setting the OrderBy
property to "DaysToComplete" works. It's worth a try.

 

[Greg]

*Grin* I think I understand what you're saying, but once I'm sure I
have it figured out (and how to apply it based on what/how things are
already built) I'll give it a shot. Thanks for the help and I'll post
back when (if) I get it working.

Greg =)