Showing month of the year in chronological order in a report.

Hi everyone, 

I am having a bit of a problem. I am doing a report to show monthly savings. 
My report is based on a query with the following fields: Date closed by month 
and Summary of savings. Here is the problem. The report shows the date closed 
by month with the sum for each month. However, the month are not in 
chronoligical order as I want them to be. Secondly, there is a random date of 
December, 1899 in the report. 

How do I get rid of the December 1899 date and how do I make the month list 
in chronoligical order?

Thanks for your help.
0
Utf
11/15/2007 8:10:01 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
2213 Views

Similar Articles

[PageSpeed] 23

The Month function will return the number of the month. For example:

 Debug.Print Month(#November 15, 2007#) returns 11

If you sort on the date field using the Month function, it should sort 
correctly.

As for the centuries-old dates, Access store midnight on the morning of 
December 30, 1899 as 0. December 3, 1899 is stored as 1.

Debug.Print CDBL(#December 30, 1899#) returns 0

   also

Debug.Print Format(CDate(0),"Long Date") returns Saturday, December 30, 1899

I'm thinking that there is a 0 or 1 getting into the date text box of that 
report somehow.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Joe" wrote:

> Hi everyone, 
> 
> I am having a bit of a problem. I am doing a report to show monthly savings. 
> My report is based on a query with the following fields: Date closed by month 
> and Summary of savings. Here is the problem. The report shows the date closed 
> by month with the sum for each month. However, the month are not in 
> chronoligical order as I want them to be. Secondly, there is a random date of 
> December, 1899 in the report. 
> 
> How do I get rid of the December 1899 date and how do I make the month list 
> in chronoligical order?
> 
> Thanks for your help.
0
Utf
11/15/2007 9:38:03 PM
>>How do I get rid of the December 1899 date  
That date means that 0 (zero) is stored in the field.  You can update to 
Null but if you have criteria on that field you may need to revise queries.  
Alternatively you can use an IIF statement in the report textbox to display 
Null if that date.

>>how do I make the month list in chronoligical order?
Add a calculated fields and use in the Grouping and Sorting.  
The calculated field would be like this ---
   Year_Mon:  Format([YourDateField], "yyyymm")
-- 
KARL DEWEY
Build a little - Test a little


"Joe" wrote:

> Hi everyone, 
> 
> I am having a bit of a problem. I am doing a report to show monthly savings. 
> My report is based on a query with the following fields: Date closed by month 
> and Summary of savings. Here is the problem. The report shows the date closed 
> by month with the sum for each month. However, the month are not in 
> chronoligical order as I want them to be. Secondly, there is a random date of 
> December, 1899 in the report. 
> 
> How do I get rid of the December 1899 date and how do I make the month list 
> in chronoligical order?
> 
> Thanks for your help.
0
Utf
11/15/2007 9:41:24 PM
Reply:

Similar Artilces:

Service work order
Does anyone have a template for a service work order? More specifically, a technology work order would be great. Just something that states the date, client name, description of problem, date completed. geek Browse around in the Template Gallery. May find something? http://office.microsoft.com/en-us/templates/default.aspx Gord Dibben Excel MVP On Fri, 7 Jan 2005 06:59:03 -0800, schoolgeek63 <schoolgeek63@discussions.microsoft.com> wrote: >Does anyone have a template for a service work order? More specifically, a >technology work order would be great. Just something t...

change fonts for specific fields in a report
A Great Plains report prints checks. The check amount field is too small and needs to be increased. How does one change the font of a particular field in a Great Plains report? If the report is a text report, you can't change the font. If it's a graphical report, click on the field and press <Ctrl-D>. "caincognito@yahoo.com" wrote: > A Great Plains report prints checks. The check amount field is too > small and needs to be increased. How does one change the font of a > particular field in a Great Plains report? > > If it is a text report, you...

having a logo on a report...
Im trying to have a company logo at the top of my report. The problem is that I only want it when the reports "CompanyFrm" field is TSWS. My CompanyFrm field is a dropdown box in the form that places the address of the company from at the top of the report. Is there a way that when the dropdown box is TSWS it places the logo at the top of the report instead of the address? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 To the OnPrint event of the report add the code Me.[LogoName].Visible = (Me.[ComboName] = "...

Sales order Processing and Invoicing Modules
Hello Folks, I have situation here. I am setting a sub-company from a parent company and have to move specific data( like cherry pick). While moving SOP tables, it looks like I have stumped over both the modules/series. When I open SOP , 'Sales Transaction Entry" it gives me an error message which says, "SOP and Invoicing Modules both installed. They do not share information. Transactions should be entered in one module only". How can I fix this issue or get rid of this error message ? Please help Regards, Run GP Utilities. One of the options is...

Monthly Recuring charges
how can we setup a monthly recuring charge for ex: we have a sheet that we keep track of all our expenses we have a telephone bill charge every 13th of the month for $25 put it we don't use everyday the spreadsheet or we may use the the excel spreadsheet twice a day we want that this charge shall automatically be entered on or after the 13th of of the month (only once each month) we have a date field in the spreadsheet hi, you might find conditional formating. see this site for more info http://www.contextures.com/xlCondFormat01.html >-----Original Message----- >how can we ...

Report writer function/calculated field question
I have a string field that consists of a number followed by a decimal followed by 5 zeroes. The number is always a whole number so I want to remove the decimal and the 5 zeroes. However, since the field is a string, I'm struggling to find a way to do that. Does anybody have any suggestions? Thanks. Zack I would use the RW_Strip user defined function. This function will return the value of the string with all occurrences of the specified characters stripped out. So you want to strip out .00000 and retrieve the remaining value? You would pass in the string field and then the characters y...

Reported error (0X800CC0F) the connection to the server was interrupted
I am receiving this error message on a client workstation, none of the other 4 users on the LAN are receiving this error. They are using Outlook 2002 connected through an ADSL modem. The user still can send and receive e-mails but this error message always appears. Any one have any ideas? Reported error (0X800CC0F) the connection to the server was interrupted Sean For those of you that have been getting "Sending and Receiving reported error .... connection to server was interrupted" as I have, try creating a new HTTP email account, send and receive a couple of messages from that...

Move Detail on Report
Hi, I used the wizard to create a form. Everything is fine except I want to have the subtotals under the group headers but the default is for the opposite. Is there a way of 'dragging and dropping' the detail line? -- Andrew 130707 On Jul 13, 6:28 pm, Andrew <And...@discussions.microsoft.com> wrote: > Sorry, no, that didn't anser my question. When looking at the report design > I have various group headers (working) and the detail after them. I want my > detail above them - any ideas? > -- > Andrew > > "Maurice" wrote: > > Andrew,...

Incorrect mailbox size reporting Exchange 2003 SP1
Hello, we are facing a little problem with one mailbox on Exchange 2003sp1 the user reached the mailbox size limit and received the messages. however, after deleting mail (and cleaning deleted items), the mailbox size is still showing 300 mb, even though the actual size in outlook is 5 mb. for other mailboxes, it is immediate, both when size increases or decreases. the only mails left are the dumpster, but it shouldn't count on mailbox quota. Does Anybody know what i could check ? I know i can recreate another mailbox and transfer mails, but i hope there is a more 'elegant' solu...

Reports
Sorry if this may seem an obvious question; I've recently started with CRM 3.0 and have been staring at it for too long! I have created a custom field on the case form called TimeSpent. On my journey to Utopia (to send e-mail automatically to client with Case Desc, Time Spent, Time Remaining) my immediate basic requirement is just to have TimeSpent as one of the columns. So, until I get to Utopia, I generate report - Export to Excel - click to drill down - and I have most of columns except for my custom TimeSpent! Can anyone help me Regards Did you publish your customizations? Hi F...

Access report in rtf format arrives in e-mail as xls file
I have a single report (with subreports embedded) that is e-mailed every day. I have been able to successfully send this report for a couple of months as an rtf file with no problems. For some reason unbenownst to me, the report formatting now shows in the e-mail as an xls file. I do not know why this has changed and have not been able to arrive at the solution as of yet. I wondered if anyone has any ideas as to why this might be happening? Your thoughts are appreciated. How exactly do you send the report? Do you do it manually or by some code? If manually, describe the steps. ...

Better reports
I want to see my sales by department, or maybe even by department and then by supplier. I also need to see what i've sold and what i've received. Does anyone have any better reports than what is in rms? Thanks, Dave Dave, We have a fantastic reporting tool. We have a new excel OLAP product that lets you slice and dice your data in Microsoft Excel. You can view sales, po's, current on hand, ect. by departments, suppliers, categories. We have a report that shows current on hand, received to date, outstanding on po's and what you've sold all in one. You can...

Show format in formula bar
Hello: On some spreadsheets I have #'s in the billions added together in cells. Sometimes it is difficult to verify the number is 10,000,000,000 because it shows up as 10000000000. My eyes get crossed counting the zeros. I'd love if the formula bar would show 10,000,000,000; however, can't seem to find a way to do this. Is this possible? No, you can't change the number formatting in the formula bar. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Native" <native_texan_03@yahoo.com> wrote in message n...

Best Practices shows 114 GB Database Size limit
I just ran a best practices scan against my Exchange 2003 SP2 Server and it shows that the Mailbox limit is 114 GB. I though the limit was going to be 75 GB in SP2? The value data in the Database Size Limit in GB registry key is 72 hex. DO I have it configured wrong? SHould it read 114 GB in the best practices scan? Any help would be appreciated. Thanks -- jgarcia 72 Hex is 114 decimal...you need to set it correctly in your registry. "mrjules" <mrjules@discussions.microsoft.com> wrote in message news:8BD7A4CB-DFA0-4485-844F-3BE6A3A56ACE@microsoft.com... >I ju...

Current Year Taxes
I put my check into Money every payday and have detailed all deductions from my check for taxes, 401k, etc. Why are that taxes that are deducted from my check not reflected in the taxes paid on "My Money Home Page"? ...

How do I do a slide show in Publisher?
I need to do a slide show from Publisher...how? No tools found to help! Publisher doesn't do slide shows. Try PowerPoint... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Susan" <Susan@discussions.microsoft.com> wrote in message news:BB3B5FA1-0DB9-4E03-8B24-234FC1B50882@microsoft.com... >I need to do a slide show from Publisher...how? No tools found to help! Susan wrote: > I need to do a slide show from Publisher > ...how? No tools ...

Help!!Getting audio files to play continuously during slide show?!
I have Powerpoint 2002, and i am having a hard time getting the music file to play continuously during the slide show. it plays on the first slide (which is the only slide that has the little speaker picture on it), but then it doesn't transition to the next slide until after the song is done, when i want the song to play while the slides are transitioning. HELP please! Mega, Have a look here and see if it helps, there are tuturials in video demonstrating the process. http://www.pptfaq.com/FAQ00047.htm -- Luc Sanders MVP - PowerPoint "MegaB86" <MegaB86...

Task 'Work
When downloading an email with an Excel attachment (I have had this happen with both '97 - 2003 and 2007 formats) I get this error: Task 'Work - Receiving' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP). The mail administrator at our ISP sees no issues on his side. If I do not download with Outlook 2007 I can go into my webmail and the email is there and I can get the attachment. It also appears in my Blackberry mailbox. However, wh...

Positions showing up instead of transactions
Greetings, I am currently using Microsoft Money Plus Deluxe and just recently (a week or two) I am experiencing a problem with my Vanguard transactions not working properly. I used to get Vanguard mutual fund and brokerage services transactions (buy, sell, etc.), but now I'm only receiving position adjustments (add x amount of shares instead of actual transaction on specific dates). I've been using Microsoft Money since 2001 so I know it can get wonky at times. I'm not sure if this is just another temporary Money problem though or whether Vanguard has changed how they handle elec...

customizations in web client not showing up in outlook client
I setup a sample customization on the opportunities screen in v1.0. It shows up fine when i pull up an opportunity in the web client. However, if I pull up Opportunities in the outlook sales client, i don't seem my button listed. Is there something I'm missing here? Hi Mike, did you follow the instructions in the CRM Outlook Customization SDK? Remember that the Outlook client uses a different customization file than the web client does. Mike "Mike Liddekee" <mliddekee@humco.com> wrote in message news:Oeuwys72DHA.2136@TK2MSFTNGP12.phx.gbl... > I setup a sam...

quantities on order by Item by store from HQ
We are currently on 1.20157 of RMS. I have 5 locations on Store operations and HQ. We need to be able to see quaintly on order by store, by item. Currently RMS lets you see the total on order for an item, but not how much is on order for each location. Is it possible to get this information? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft...

Please Help..Error in Financial Statement Reports
Hello all, After we finished year end closing procedure we tried to print Balance Sheet and Profit & Loss statement report but we surprised from the results.. The balances are not correct and some of the accounts didn't appeare in the report !! Although we made Reconcile, we get nothing new :( While when we print a trial balance report all the account balances are OK and we checked the closing journal it gives a correct balances ! the question is: whats the wrong in the financial statements report.. why it gives error and dosen't show sales accounts ?!! Please help. We stopp...

Custom Batch report
We are looking to get a report as follow: 1. Filter by Batch Number 2. The following columns in the report: batch #, Transaction #, Customer Name, Tender Type, Total amount. Getting sub totals by tender type is helpful addition. We will be happy to get quote for the job. Thanks. shauli@usauthentic.com Dover, We have a report that does just that. If you would like more information please email sales@rite.us. Ryan Sakry Program Manager "Dover" <Dover@discussions.microsoft.com> wrote in message news:71A52C0F-3024-4459-902B-CF41DD06C4D6@microsoft.com... > We are...

report record source
Access 2003 I am trying to change the record source of my report using the below code. I get error 2191. What is the correct way to set the record source of a report? If UnitNo = 0 Then Dim strsql3 As String Dim strsql4 As String strsql3 = "SELECT t040Project.ProjectID, t041ProjectDetails.ProjectDetailsID, " & _ " FROM t040Project INNER JOIN (t041ProjectDetails INNER JOIN t000Facts " & _ " ON t041ProjectDetails.UnitID = t000Facts.UnitID) " & _ " ON t040Project.ProjectID = t041ProjectDetails.ProjectI...

Is anyone on here with DVS Reporter???
Hi, I work at this german firm now and they use the DVS Reporter as a add-in in Excel. I have never used this before in the US. Do you kno whether there is some kind of support forum for it? Thanks, Mari -- marias ----------------------------------------------------------------------- mariasa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3172 View this thread: http://www.excelforum.com/showthread.php?threadid=51680 ...