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?
0
11/8/2006 9:01:01 PM
pos 14173 articles. 0 followers. Follow

1 Replies
519 Views

Similar Articles

[PageSpeed] 30

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 Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information.


Database Badger wrote:
> 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?
0
glenn9672 (1267)
11/8/2006 11:55:27 PM
Reply:

Similar Artilces:

Using Publisher for long reports
Hi. I write long reports (50 pages) in which layout and style are very important. I'm thinking of switching from Word to Publisher since Word's management of pictures and layout drives me wild. Can anyone advise me if Publisher might be the right choice? Does anyone have some long document templates they could show me? -- Giles It sounds like Publisher would be perfect for you since you indication that the layout is important. I can't help with a sample tho because (1) the longest I've done is about 24 pages and (2) my newsletters contain personal contact information and...

I just activated office2003 S&T ver, cannot reply to emails
my reply icons are grayed out, I can see all incoming msg, but cannot reply In news:28E0E702-5CE2-4C2A-B033-EC618ECF90EE@microsoft.com, txcowboy <txcowboy@discussions.microsoft.com> typed: > my reply icons are grayed out, I can see all incoming msg, but cannot > reply What type of e-mail account do you use? Are you running all the Office/Outlook updates, including SP1? I got it resolved. Don't know how for sure. I am running MS office outlook 2003 I think I needed to authenticate to MSN Passport by login in. before it allowed me to do replies. All this security and I ...

Spin Box with Dates and Lookup
Hi, I'm trying to design a spin box function which pulls in data from monthly tabs, the month being determined by a spin box. I have monthly tabs Jan-10 to Dec-10 all containing an identical table with different data in. The user needs to be able to change the month of lookup using a spin box function but I cant get it to work properly. Thanks in anticipation. Hi, You are not very clear about your question. Anyways, try this Right click on the spin control box which you have drawn ad give the lower and upper limits are 1 and 12 respectively. In the cell link box, ...

Modifying Activity Views
We have customived our Activity view using the undocumented back door means ( http://crm/tools/viewEditor/viewManager.aspx?id={00000000-0000-0000-00AA-000010001903} ) and now have been asked to modify associated activity views, like Contact Activities to match. Does anyone have the information on this? Thank you in advance, Scott From an earlier post: Just type for the "My Activities view" the following URL in your Internet > Explorer and replace "crmserver" with the name of your server. > http://crmserver/tools/viewEditor/viewManager.aspx?id={00000000-0000-0000-00...

Item's Last Sale Date
Hi there I'm creating a smartlist report that will display the item details but not sure which inventory table I should use to get the Last Sale date of an item. Any ideas? thanks. You can try this query to fetch the item number with the highest date of sale (or last sale date): SELECT ITEMNMBR, DOCDATE FROM IV10201 GROUP BY DOCDATE, ITEMNMBR HAVING DOCDATE =3D MAX(DOCDATE) AND ITEMNMBR =3D 'xxx' You can change 'xxx' with your item number. I've not tested this fully so you may want to run this in your environment and make sure that this query will work for every ...

Specific Age Query for a Date Range
Table = Personal Field = Birthdate I need to create a query to show who is of a certain age for January 1, 2008 to December 31, 2008. For example, I need to know who will be 50, 60, 65, 70 and 75. I would like to create the query so that when I click the query to open it, I have to input the age. I've done other queries to show who is of a specific age as of today: AgeYears: DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in Years] ... but I cannot figure out how to pull a report...

Exchange 2003 SBS Attachment filtering in admin
Folks: I have set up SBS 2003 / Exchange on our server some time ago. I have been trying to find the location to add an additional attachment to the exclude list on incoming mail. (Attachment filtering). I have looked for some time and cannot find the UI to do this in the system manager. Could someone point this location out to me? Thanks d "D S" <nobody@nowhere.com> wrote in message news:uPeCkbUrGHA.4992@TK2MSFTNGP05.phx.gbl... > Folks: > > I have set up SBS 2003 / Exchange on our server some time ago. I have > been trying to find the location to a...

merging into Word with filtered data
Hello, I'm trying to do a merge in Word using data from Excel. However, I don't want to merge using data from the whole worksheet, but just filtered data (all people from London - place is one of the fields in the worksheet. I have no idea how I might do this. Any help gratefully received. I believe you can accomplish this from Word by using "Query Options" and selecting to merge only those records you're seeking. HTH PC "Italian Pete" <ItalianPete@discussions.microsoft.com> wrote in message news:3E62FCDC-42EC-48DB-92AC-857B224F085F@microsoft.c...

Message Filtering on MS Outlook 2000
Hi I'm Leo I have a problen on my MS Outlook Message Filtering, this is the scenario. User A sends a message to User B (inside my organisation) and User C (outside) then the message goes to User B Inbox, then User C replies to User B and the message goes to Junk Folder. This is not usually heppening when User C sends Email on User B, normally it goes to User B inbox. We have a Rule that filters the Email Address of the sender so I think that the MS Outlook filters the message thats why it goes to Junk Folder. My question is how did MS Outlook filters messages even if we do not have Rul...

New type of activity
Hello, I want to create a new type of activity. For eg., like the way we have types in Phone Task, Fax, Phone Call,etc. we need to have 1 or 2 more types. Is it possible? Regards, Venkat Venkat; New activities are not currently possible without using the SDK. MSFT has promised more flexibility with activities in v2.0 (due Q1 2005). Dave The only way to do this would be to create the new activities in a different table and link somehow back to CRM. A number of companies have developed solutions like this. As said this feature has been mentioned as a possible for the next crm release a...

Calendar dates in all users outlook
G'Day All, I am running E2K and all users are running Outlook XP on their desktops. Without visiting all users computers how can I update all users' Outlook Calendar to show the days/dates the office will be closed? Thanks I have the same problem. The only workaround I could find was to send out an email meeting invitation to everyone and instruct them to click accept (which they eagerly do if it's a holiday). If you find a better way let me know! Chris ...

Changing text based on criteria in an access report
Hello, I've got a textbox in a report, some records of which may contain text within { }. I need to find some way to format only that text (which may be in the middle of a string) so that it appears underlined and so that the brackets disappear. To illustrate, I posted a 5KB graphic here: http://img219.imageshack.us/img219/3523/jexamplemb2.png Does anybody have any suggestions or ideas? Thanks in advance! Mitch Access 2007 can format the text for you, using standard HTML for the underlining. Use the Replace() function to swap the braces for the HTML tags. Earlier versions of Ac...

Media player in active X
Quick question if I may... I need to play two simultaneous sound files in Excel, and as I cannot have two userforms at the same time, I'm trying to have one play as a userform in mediaplayer and the other play as a windos media player ActiveX object embedded into the sheet (using Office 2007). However when I try to instruct the windows media player to begin playing it's file it hits a bug and refuses to budge. Am I attempting the impossible or just missing the obvious? Many thanks Private Sub UserForm_Activate() Dim Mfile As String Mfile = "c:\video\crowd.wav" MP....

Group By Flag Status and then Received date
I want to group first by flag status and then by Received date, but have the same collapsable headers available in the Received that you get if you group first by Received date. It looks like it may not be possible, but thought I would post to see. Thanks! Following is the example. So: Group: Flag Color Unflagged (Then emails by date with this flag color grouped as the following) Date: Today Date: Yesterday Etc. Instead of Group: Flag Color Unflagged (Where the groups by date are specific including the time). Date: 6/9/2006 4:18 PM Date: 6/9/2006 4:12 PM ...

Can I calculate a field with 2 different filter criteria in one q
Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE "abc" in one query, but I can't seem to figure this out, but, I'm a novice at this. Best I can figure is 2 different queries, then a 3rd query to bring the two together. Thanks -- Jim jimd wrote: >Hi, I have a data source table with a customer name field and various metrics >fields, I'd like to sum metric &...

Grabbing Info from a TextBox on a Report
We want to add a TextBox to a report that is viewed online. The data collected in this TextBox will be used to adjust the report filter. We want to allow our users to enter as much of a Customer Name as they want after they see the report with all Customers shown. We then want to use this info to change the Report Filter. We have never tried this before. For some reason, we cannot see the data in the TextBox in our VBA code. It appears that Access Reports behave differently than Access Forms as far as grabbing data from TextBoxes. We must be missing something. Does a...

MaximizeRestoredForm for Reports
I took the procedure from http://www.mvps.org/access/api/api0022.htm and changed it to use for reports. I tried calling it from the open event and the activate event and in both cases it places the report in the upper left corner, but it doesn't size it. But if I put code on a button to open the report and then call this procedure it works as expected. Why won't it work from within the report? Jennifer ...

NEed report of income and expenses broken down
I'm using Microsoft Money 2006 and I entered all of my checking account transactions for last year and categorized everything. Now I need a report of the income and of the expenses broken down by category, with the transactions listed. If I go to reports, all I can get is a pie chart or bar chart. I need a printed report with numbers to give to the accountant tomorrow. I was able to export a report to a spreadsheet, but all it did was show the total expenses in each category. What I need is a report showing the categories for income and for expenses, list the transactions in each ca...

Need to sort dates before 1900 in proper order
I need to sort dates before 1900 in proper order -- ones after 1900 are all fine but any dates like 03/29/1865 will always sort by month and day but NEVER the year except in the month area. Don't know if I am making sense -- but will be like: 03/29/1865 03/29/1866 03/29/1873 04/01/1863 04/01/1868 etc. Thanks, Judy M. I can provide a chunky workaround, until someone provides a better answer: Assuming your 5 sample dates are in cells A1 ~ A5, and that single digit months and days are always expressed with a leading zero: In cell B1, enter this formula: =VALUE(MID(A1,1,2)) In C1, ente...

eCommerce / RMS Integration Project
We are a retailer with 4 stores running RMS and a website running php/mysql. We were considering re-doing our website with storefront.net since our current system isn't as reliable as we'd like it to be and storefront has some nice additional features. Mainly we want our web to look and act more like our stores. We want to manage inventory from RMS rather than a proprietary web interface. It would be nice to have our customer's in RMS as well. The Newestech integrator should be more than adequate. I would like to hear some feedback on Storefront.Net. I've heard some ba...

Track Activities in Public Folders
I want to put all of our customer contact information in a public folder. I also want activities of each user related to contacts. I know that tracking is configured in the properties of a folder. If the folder is in Public Folders\Favorites, can tracking be configured at the user level? If yes, what will happen when the user synchronizes with the server? If not, is there some other way to accomplish tracking activities related to contacts? ...

Adjust CRM view to filter by workflow name
I want to see my leads that have NOT fired off a specific workflow. I’ve adjusted my view from within the Lead by adjusting the Filter criteria to find: System Job (Regarding): System Job Name <> Lead Drip Marketing. Here’s a screen shot to help: http://www.beringer.net/images/LeadDripMarketing.gif There is some filtering going on, but it is not working as expected. Has anyone done this? Thanks for reading. Hi, Could you try to add another condition to your advanced find; this should be that System Job Type = Workflow. Let me know if it works. "StingRayYellow" w...

RMS Store Operation Manager
Hello Everyone, I just finish to install on my laptop to test the RMS 1.3, it installed MSDE 2000 and I created the Database with RMS Store Administrator, but when I try to connect with the RMS Store Manager I get the following error: Not Found Error 201 : The Cashier was not found or the password is incorrect. Username: sa Password: sa DB: RMSSample Rommel J. Jimenez When logging into SO Manager or SO POS by default use the following: User Name: 1 Password: password Rob "Rommel J. Jimenez" <rommeljimenez@cantv.net> wrote in message news:O5RUeHADHHA.996@TK2MSFTNGP0...

crystal report is blank
i have 3 machines I run Store Ops Manager 2.0 on. 2 of the machines I can run my crystal reports just fine but on the other machine my crustal reports generate but the data is always zero. any ideas? I'm sure it's something simple. thanks! This is a multi-part message in MIME format. ------=_NextPart_000_009A_01CA985D.46025960 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Sammy, Where is the machine pointing to for its Crystal reports, the server or = the local machine in SO Admin | File | Configuration | Pat...

Counter to get total number of lines in a Modified Report
Dear All, Unlike other modified report, the cheque printout do not have field that have Line Item Sequence number, so I unable to find out the total number of lines in a cheque printing. FYI, the cheque printing (3 Per Page/No Stub) output to printer and also output to a text file (Comma delimited) and this text file must have a field (counter) that indicate the total of number of lines printed. Or at least, can you please advise me on which fields that indicate total numbers of cheque printed? with this field contain I can compute the total lines printed. May be I can use ...