Drew Wutka
DWUTKA at Marlow.com
Sun Mar 8 14:00:01 CDT 2009
No argument on any count there! ;) I was just teasing, I just whipped up the example, wasn't going to for 100% optimization! ;) it was accurate though....for what was asked. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo Sent: Saturday, March 07, 2009 5:35 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Figuring the date No! He is trying to do 100% accurate and that is laudable. Besides which he is going to buy me a beer! These are my principles. If you don't like them, I have others! Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: 07 March 2009 21:36 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Figuring the date Picky picky... ;) Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Saturday, March 07, 2009 4:12 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Figuring the date Hi Drew, the DateSerial geek Adding the count of days to the day parameter of DateSerial and not to the resulting date, it can be reduced to: Function MondayDate(intYear As Long, intWeek As Long) As Date MondayDate = DateSerial(intYear, 1, 1 + 7 - Weekday(DateSerial(intYear, 1, 1), vbTuesday) + 7 * (intWeek - 1)) End Function For everyone else: Note Drew's clever use of vbTuesday to turn Weekday into a kind of modulus function. If you would prefer vbMonday as the parameter in Weekday, you would have to use modulus or you would an additional week when Jan. 1 is the first day of the year: MondayDate = DateSerial(intYear, 1, (8 - Weekday(DateSerial(intYear, 1, 1), vbMonday)) Mod 7 + 1 + 7 * (intWeek - 1)) /gustav >>> DWUTKA at marlow.com 06-03-2009 23:46 >>> Function MondayDate(intYear As Long, intWeek As Long) MondayDate = DateSerial(intYear, 1, 1 + (7 - Weekday(DateSerial(intYear, 1, 1), vbTuesday))) + (7 * (intWeek - 1)) End Function Watch for word wrap there, the code inside that function should be all one line. It's the date of January 1st of whatever year, offset by when the first Monday is, the 7-Weekday bit, plus 7*one less then the number of weeks. So if you put MondayDate(2009,1), you'll get 1-5-2009, the first Monday of the year. MondayDate(2009,52) and you'll get 12-28-2009, the last Monday of the year. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross Sent: Friday, March 06, 2009 2:38 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Figuring the date Okay, I have answers to my questions - they define Week 1 as the first full week of the year and the week is defined as Monday to Sunday. Any thoughts on how to get that Monday date when I know the year and the week #? Jennifer -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross Sent: Friday, March 06, 2009 12:34 PM To: AccessD List Subject: [AccessD] Figuring the date Hey Everyone, I have two pieces of information - the year and the week # within the year, for instance 2008 13 is the 13th week in 2008. I have questions out to the client about how they define the week (Sun-Sat, Mon-Sun) and how the first week of the year is defined - week that January 1 falls in or first full week of the year. Anyway - what I need to do is create a function, unless one already exists, when fed these two pieces of information returns the Monday date (short date format) for that week. Any help is appreciated. Jennifer -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.