Get Start date of Week number and Year

I’d like to build the following expression in my query
GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
So if EnteredDate = 11/3/2009 the function would return 11/1/2009

But GetStartWeekNumber does not exist as an Access Built-In Function.
Is there another way to do this as an expression in a query?
I’m not familiar with creating my own functions.

Thanks.
0
Utf
11/16/2009 5:15:03 PM
access 16762 articles. 3 followers. Follow

10 Replies
1515 Views

Similar Articles

[PageSpeed] 27

That would depend on how you define the start of the week...

One option would be to get the day-of-week number of the date (in my 
system/setup, Monday is day 2), then subtract one less than that from the 
date to get the date on the start of THAT week.

Yes, you can do that in a query expression.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"h2fcell" <h2fcell@discussions.microsoft.com> wrote in message 
news:F1313536-3DC9-43CC-AC64-A881338EF12F@microsoft.com...
> I'd like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
>
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I'm not familiar with creating my own functions.
>
> Thanks. 


0
Jeff
11/16/2009 5:41:56 PM
I think this will help:
Weekday(date, [firstdayofweek])
Returns a Variant (Integer) containing a whole number representing the day
of the week.
To get Sunday to be the first day of the week enter 1 for firstdayofweek OR
the constant vbSunday

for the date 3rd nov 2009 this returns 3 So you know Sundays 3 days back
from the date you entered

so then use dateAdd to get the date three days back and return it from the
function

Jim


"h2fcell" <h2fcell@discussions.microsoft.com> wrote in message 
news:F1313536-3DC9-43CC-AC64-A881338EF12F@microsoft.com...
> I'd like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
>
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I'm not familiar with creating my own functions.
>
> Thanks. 


0
Jim
11/16/2009 5:46:52 PM
H2fcell -

Try this:

=DateAdd("d",-DatePart("w",[EnteredDate])+1,[EnteredDate])

This will check the day of the week (1 = Sunday) and subtract the number of 
days less one from the EnteredDate.  So if the current day of the week is 
Tuesday (DatePart will return 3), we subtract 2 days from the entered date.

-- 
Daryl S


"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:21:07 AM
FirstDayWeek: [EnteredDate] - Weekday([EnteredDate]) +1

The above assumes that you want Sunday to be the first day of the week.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:23:01 AM
Try this --
DateAdd("d",-Weekday([EnteredDate])+1,[EnteredDate])

-- 
Build a little, test a little.


"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:29:03 AM
Seems that my first reply didn't make it.

FirstDayOfWeek: [EnteredDate] - Weekday([EnteredDate]) +1

The above will work in a query. It assumes that Sunday is the first day of 
the week.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:32:02 AM
Try this:

    [Entered Date] + 1 - Weekday([Entered Date])

Weekday returns 1 for Sunday up to 7 for Saturday, and when you add or 
subtract integers to and from dates, the integers count as days forward or 
backward.

-- 
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:36:19 AM
DateAdd("d",-(Weekday([Some Date])-1),[Some Date])

will give the previous sunday of any date


-- 
Wayne
Manchester, England.



"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 5:38:01 AM
Here is a procedure that will calculate the first day of the week for the 
given date.  Copy and paste it into a module and then you can call it in your 
forms, report, queries.

'---------------------------------------------------------------------------------------
' Procedure : GetFirstofWeek
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the date of the first day of the week for a given date
' Copyright : The following may be altered and reused as you wish so long as 
the
'             copyright notice is left unchanged (including Author, Website 
and
'             Copyright).  It may not be sold/resold or reposted on other 
sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' dtDate    : Date to find the start of the week of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' GetFirstofWeek(#10/2/2009#)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' 
**************************************************************************************
' 1         2009-Nov-16                 Initial Release
'---------------------------------------------------------------------------------------
Function GetFirstofWeek(dtDate As Date)
On Error GoTo Error_Handler
    'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1) 'Returns 
the Sunday
    GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 2) 'Returns 
the Monday

Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & 
"Error Number: " & _
    Err.Number & vbCrLf & "Error Source: GetFirstofWeek" & vbCrLf & "Error 
Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"h2fcell" wrote:

> I’d like to build the following expression in my query
> GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> 
> But GetStartWeekNumber does not exist as an Access Built-In Function.
> Is there another way to do this as an expression in a query?
> I’m not familiar with creating my own functions.
> 
> Thanks.
0
Utf
11/17/2009 6:09:02 AM
All I can say is WOW! This date math is simple yet elegant.
Thank you very much.  This is what I was looking for.


"Andy Smith" wrote:

> 
> Try this:
> 
>     [Entered Date] + 1 - Weekday([Entered Date])
> 
> Weekday returns 1 for Sunday up to 7 for Saturday, and when you add or 
> subtract integers to and from dates, the integers count as days forward or 
> backward.
> 
> -- 
> * Please click Yes if this was helpful *
> Andy Smith
> Senior Systems Analyst
> Standard & Poor''s, NYC
> 
> 
> 
> "h2fcell" wrote:
> 
> > I’d like to build the following expression in my query
> > GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate]))
> > So if EnteredDate = 11/3/2009 the function would return 11/1/2009
> > 
> > But GetStartWeekNumber does not exist as an Access Built-In Function.
> > Is there another way to do this as an expression in a query?
> > I’m not familiar with creating my own functions.
> > 
> > Thanks.
0
Utf
11/17/2009 2:03:01 PM
Reply:

Similar Artilces:

Getting email addresses.
Hi, I have a customer who wants to get a list of all the email addresses that are passed through his exchange server, both inbound and outbound. They are using the tracking centre so I was wondering if there was a way of getting the info out of here. Has anyone done this or is it even possible? Is there a better way? Please let me know, Thanks, Chris This information is stored in message tracking logs and in SMTP logs, if logging is enabled. I think your favorite spreadsheet app will help yo with extraction. Logs can be found in \\servername\servername.log. Chris Gradden wrote...

Sequential Number Generator, Saving, and Linking
Hello, not totally familiar with Excel and trying to accomplish th following within our sales department: 1. We have two basic Excel files that we have to access on a share drive every time we issue a quotation: FILE A: This is an Excel quotation form already built. FILE B: This lists out the existing quotations that have been issued listed by reference number. It includes basic fields like 'Sale Person', 'Product' and 'Date', etc. Every time we issues a quote we fill out the nessary information i FILE A, reference FILE B to determine the next sequential number. W s...

When printing a Snapshot I get extra pages
When I print the snapshot report i get extra pages, I have gone back and trouble shooted the page breaks and I still get the extra pages. The page numbers are not on the extra pages so I don't understand how I am getting them/ ...

calculate dates #2
I am trying to create an application to calculate a date that is 2 weeks from today but I never want the date to be a weekend or holiday. The results I want to run on someones desktop without them having to do anything. Any thoughts? Use =WORKDAY(TODAY(),14) WORKDAY is part of the Analysis Toolpak add-in, so you may to install that -- HTH RP (remove nothere from the email address if mailing direct) "kevin.raway@lifefitness.com" <kevinrawaylifefitnesscom@discussions.microsoft.com> wrote in message news:A790A02C-0A02-42F0-9684-719436798CD6@microsoft.com... > I a...

ReportViewer
Using ReportViewer control v9 in .aspx page - When displaying date type parameter field in my report nothing happens when calendar icon is clicked (no calendar page, no error) in ie 8. FoxFire works. Same report works fine when viewed directly on the report server in ie8. Resolved myself. Sloppy coding on the Master page. <form> tag out of place in table structure. "Pete Hay" wrote: > Using ReportViewer control v9 in .aspx page - When displaying date type > parameter field in my report nothing happens when calendar icon is clicked > (no calen...

Excel VBA
Hi All, I want to be able to use a msgbox that asks a person what date it is? When the person enters say 19/08/04 and presses ok, another spreadshee willl now have this date entered. What im trying to do is in the end is be able to write the date in an sleect a task. So each time i do a task i can say what day and how man times. Any help would be great Im novice at best.: -- Message posted from http://www.ExcelForum.com Hi try something like the following: Sub foo() Dim res_value Dim target_wks As Worksheet Set target_wks = ActiveWorkbook.Worksheets("Tabelle2") Do res_value...

numbering #2
Is it possible to apply numbering (invoice or booking numbers) to sheets inside of a book. Four people using the same books and we want to put number on the sheets without becoming a number management nightmare Like adding a series of numbers like invoice number Each sheet having it's own number in the series but not repeting on different sheets. Basically generate an invoice number on the sheet when copied from a templete or at print time ...

Why is my favorite folders pane gone? How do I get it back?
They say you can't do this. In the little buttons on the bottom, click on the envelope that corresponds to "Mail." That will get the view back that has the favorite folders and all mail folder in it. Click on the little folder to go to regular folder view. Cappy "zfolwick" <zfolwick@discussions.microsoft.com> wrote in message news:14EB8D60-5483-4978-80C4-38A6DD598E91@microsoft.com... > They say you can't do this. Geez. . . so. . . it's a problem with the ID 10 T working the keyboard that's taking the short bus. Thanks, man. "Cappy&q...

Page numbering worksheets
Is it possible to number the pages in a workbook if l have multiple worksheets? I would like to number each worksheet within the workbook but l need the numbers to flow on from the previous sheets so l don't have to manually change the number on each worksheet. thankyou "H" <Hayley.Slater@emap.com> wrote in message news:06f501c3505e$df329d50$a001280a@phx.gbl... > Is it possible to number the pages in a workbook if l have > multiple worksheets? I would like to number each > worksheet within the workbook but l need the numbers to > flow on from the previous...

Display page number in a cell
Is it possible to display the page numbering in a cell instead of header/footer? This would require some codding to find the pagebreaks and enter a sequential number in a cell above each page break. Have a look at this thread. http://snipurl.com/9ffk Note David MCRitchie's links to Myrna Larson code. Gord Dibben Excel MVP On Wed, 29 Sep 2004 07:13:04 -0700, sbuddy <sbuddy@discussions.microsoft.com> wrote: >Is it possible to display the page numbering in a cell instead of >header/footer? ...

How do I get Outlook2003 to open folders when new mail arrives?
Outlook 2003 / ms Exchange 2007 Hi How do I get Outlook2003 to open folders when new mail arrives? Background: I have just changed my profile so that we now use Ms Exchange(2007) with our Outlook(2003). In my previous profile (which used .PST files), when new mail arrives in a folder (having been placed there by a rule), Outlook would automatically open each folder when it puts new emails into that folder. This would mean that previously I could see at a glance where new emails have arrived (because the foldername has gone bold, with the number of unread emails also showing in brackets). ...

0 number
Hi, social security number has 7 numbers. However, when I type 0454636, 0 will be gone. Ex: Type 0454636. It will become 454636. I have almost 8 columns stored social security numbers. Please show me how to fix the problem. Thank you Loi Try format cells -- custom and type 0000000 in the box. >-----Original Message----- >Hi, > >social security number has 7 numbers. However, when I type >0454636, 0 will be gone. > >Ex: Type 0454636. It will become 454636. > > >I have almost 8 columns stored social security numbers. >Please show me how to fix the prob...

Date format with day of week and date
I have Excel 2003 and want to figure out how to format a cell where the date and day of week appear...i.e. Wed 10/19/2005 I know how to make the date formats change but cannot find one that offers both the date as well as the day of week...if there are no options for it, what is the format for customizing it to do this? Thanks! Kathy One way is to use a custom format DDDD M/DD/YYYY "Kathy" wrote: > I have Excel 2003 and want to figure out how to format a cell where the date > and day of week appear...i.e. Wed 10/19/2005 > > I know how to make the date format...

Calculating Days From Dates and printing letters.
I wonder if anyone can help me? Calculating Day of Week I am trying to use Outlook as my contact management software in the entertainment business. I have several hundred potential venues that run on different days in the week. When I create a new contact I would like to be able to; Input a date the venue has booked. This could be in the past or the future. From the date I have input have outlook calculate which day of the week the venue runs and store this information. Later I want to be ale to pull up for example; all venues that run on a Wednesday in a specific county Printing letters...

The number zero
I have part numbers that begin with zero. How do I keep the zero present in the cell? Judy, Start your entry with a single quote mark, or format the cell for Text prior to entering your data. HTH, Bernie MS Excel MVP "Judy" <anonymous@discussions.microsoft.com> wrote in message news:44B9D6D2-B169-4ABE-BBFF-2C615585DB1A@microsoft.com... > I have part numbers that begin with zero. How do I keep the zero present in the cell? ...

Excell 2007 Dates and combining 2 cells
I have created a date in Excel with the format of 03/01/01 in the form of mm/dd/yy Sometimes it will display the date in the cell and sometimes it will display a 5 digit number. Also, even if it displays correctly in a cell when combining 2 cells (1 cell being the word date- and the other cell being the date as 03/01/01in the form of mm/dd/yy The combination will be the date and a six digit number. Example Date-16425. What do I need to do in these situations to that it will display the date correctly in one cell( 03/03/01) and display the date correctly in combined cells (Date- 03/...

High number of page faults per second, but has enough memory?
Hi, I'm having a problem with an application, it easily has enough physical RAM but it's generating a very large number of page faults (2000 per second). Does anyone know why this might be? ...

numbers formating problem
I type 10 I got 1 I type 11 I got 1.1 Where from this problem cames? This is not a formatting problem, the value is what you see; 1, not 10. Tools > Options menu, "fixed decimal" is selected. HTH. Best wishes Harald "barsha" <barsha@discussions.microsoft.com> skrev i melding news:A62D8395-FD84-40F3-A867-B391D5956A57@microsoft.com... > I type 10 > I got 1 > I type 11 > I got 1.1 > Where from this problem cames? > Hi Barsha, Try: Tools | Options | Edit and uncheck the fixed decimal option. --- Regards, Norman "barsha" <...

CRM 3.0
I would like to move the ordernumber middle part to a larger number to match the next available number in the existing paper order system. Where do I find the row/column in the database to change the seed or next available number? ...

CRM 3 fiscal Year Setup Modify
Hi i would like to modify the setup of the fiscal year in CRM 3.0 can some one guide me how can i do that. regards Nader ------=_NextPart_0001_7F19AB8E Content-Type: text/plain Content-Transfer-Encoding: 7bit NaderYousef@discussions.microsoft.com wrote: > i would like to modify the setup of the fiscal year in CRM 3.0 can some one > guide me how can i do that. Fiscal year settings are located in Settings --> Settings --> Fiscal Year Settings. -- Tao Yue Microsoft CRM Development This posting is provided "AS IS" with no warranties, and confers no rights. ------=...

CRM Security Service not starting
I recieve an error in the event log "Error: An error occurred while attempting to update the SCP (Active Directory update). -2147016656 (0x800720030) Description: There is no such object on the server." I am running the CRM 1.2, Crystal 9, all on the same Domain Admin level ID, SQL Server is installed on a seperate server with the same domain admin id running the service. I have not installed any SPs. I have the correct MDAC 2.8 version. the Build Version table contains Build Date Timestamp BuildNumber BuildQFE 8/23/2005 7:08:01 PM <Binary>...

Decimal numbers to time format.
Hi there, I'm sure this is a simple one for you guys but I'm not having fun trying to get to the bottom of it. I have two cells: A1 - contains 47 (formatted as 'number to two decimal places') B1 - contains 12 (formatted as 'number to two decimal places') I have other cells formatted for time as [hh]:mm:ss How can I fill C1 with 00:47:12 (formatted as [hh]:mm:ss) - with the 47 and 12 being pulled from A1 & B1 Cheers Lee On Tue, 18 Dec 2007 12:21:54 -0000, "Lee Grant" <lee@inspirationcomputers.com> wrote: >Hi there, > >I'm sur...

start menu all programs
Hello Can someone please remind me how to organise the programs in 'all programs' in the Start menu into folders. I've done it before but cannot find out how to do it again. Thanks KK Hello KK, The tutorial below can help show you how to organize the Start Menu "All Programs" area. http://www.vistax64.com/tutorials/79571-start-menu-shortcuts.html Hope this helps, Shawn KRK;1251447 Wrote: > Hello > > Can someone please remind me how to organise the programs in 'all > programs' > in the Start menu into folders....

4 attachments in one email gets sent in 4 messages
I attached four files to one message and they were sent as four different email messages. How do I get them to be sent in one message? Outlook 2000 ...

Date Parameter
How do I setup a parameter in a query for start and end dates on a bithday field based on just the month and day of the birthday field? Example Birthdates: 01/01/06; 02/01/06, 01/31/05, 06/01/69 If user imputs range as start date of 01/01/90 and end date of 01/31/2007, I need to return records with birthday of 01/01/06 and 01/31/05 regardless of year. On Tue, 8 May 2007 11:13:17 -0700, dh1069 wrote: > How do I setup a parameter in a query for start and end dates on a bithday > field based on just the month and day of the birthday field? > > Example Birthdates: 01/01/06; 02...