Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2011, 08:39 AM
Authorized User
 
Join Date: Sep 2010
Posts: 45
Thanks: 12
Thanked 0 Times in 0 Posts
Default How to do a summary data by month if the field format is mm/dd/yyyy

Hello Everyone,

I need you help.

I want to do a report which shows the total student enrolled number of each month, and it bases on the student enrolled date. But on the enrolled date field, I set up the format is mm/dd/yyyy which I have to(I use calendar).

So, how can I do the number of student enrolled by month on query and show on the report?

Thanks,
Cindy
 
Old August 23rd, 2011, 04:07 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

If it is a data field then you should not be storing it formatted as text). If stored as a date/time data type then tt is a number without any date formatting. This allows your to format the date as needed when you display it.

I will assume that the enrollment date field is properly stored as date/time data type.


What I do is create a calculated field something like this:

EnrolledYYYYMM: Format([enrolled_date],"YYYYMM)

Now you can group on the feuild and get yuour count for teh primary key feild.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
The Following User Says Thank You to HiTechCoach For This Useful Post:
cindyl (August 25th, 2011)
 
Old August 25th, 2011, 10:33 AM
Authorized User
 
Join Date: Sep 2010
Posts: 45
Thanks: 12
Thanked 0 Times in 0 Posts
Thumbs up Thanks

Thanks, I will create another field to catch the month.

Cindy
 
Old August 25th, 2011, 12:10 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Just to be clear, I am not recommending adding a field to the table but a calculated field in the query only. The data will not be3 stored. Only calculated as needed.

I would include the year with the month. This will allow it to sort better. is the data range is over 12 months and/or span multiple calendar years
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert dd/MM/yyyy to MM/dd/yyyy Rg2005 VB.NET 2002/2003 Basics 2 November 7th, 2008 06:42 PM
How to pull Month & Year from Date(dd/mm/yyyy) istcomnet Classic ASP Basics 2 April 22nd, 2008 06:22 PM
Date dd-mm-yyyy John2112 SQL Server 2000 2 March 16th, 2007 05:15 AM
mm/dd/yyyy to dd/mm/yyyy Ashleek007 Beginning PHP 2 September 27th, 2006 06:35 AM
convert "MM/dd/yyyy" to "dd/MM/yyyy" xin56 General .NET 4 March 28th, 2005 05:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.