Date Filtering in RMS Active Reports

My company is open past midnight. This causes terrible headaches for me and reporting since RMS uses the standard 24 hour day, yet I need sales that occur after midnight but prior to 3 am to appear on reports for the previous day.

I have a datetime field that I am converting using the formula Formula = "CONVERT(nvarchar, ViewTenders.Time, 22)" so that in the active report it looks like 11/08/06 1:54:00 PM. In the active report, it's vbDataType is defined as vbString.

I can not get the filter(s) to allow me to show dates correctly. What I'd like to see is all transactions from 6:00 AM on one day thru 3:00 AM the following day. example:

11/07/06 06:00:00 AM thru 11/08/06 03:00:00 AM

The problem is that it keeps showing me results from 11/07/06 12:00:00 AM (midnight) because 12 > 6. grrrrr....

Initially I had the filters set as follows: Begin Filter FieldName = "Time" FilterOp = reportfilteropLesserEqual FilterLoLim = "11/08/06 02:59:59 AM" FilterHilim = "11/08/06 02:59:59 AM" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

Begin Filter FieldName = "Time" FilterOp = reportfilteropGreaterEqual FilterLoLim = "11/07/06 06:00:00 AM" FilterHilim = "11/07/06 06:00:00 AM" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

Anyone able to tell me where I'm going wrong here?

Additionally, I'd love to find a book somewhere that contains information about these Active Reports. Anyone have some suggestions?

How about creating a calculated field (FORMULA) that generates a date only ( no time needed ) "SaleDay". You would use the DATEADD function to subtract 3 hours from the transaction time, then either a CONVERT or DATEPART to generate the resulting date. You would still want to include the actual transaction time, but by using only the date you would even be able to use the column for grouping...

There is no book. The reports are based on SQL, so SQL Books OnLine is a relly good reference. Other than that, there are a bunch of sample files that you can use for comparison.

There is a "Customization Guide" available to partners, and you can probably pick up a few things in the Knowledge Base, but if you know SQL it's pretty easy to figure out reports...

Glenn Adams Tiber Creek C> My company is open past midnight. This causes terrible headaches for me and