?
Solved

Ms Access 2007 ADP, USING SQL SERVER to convert date to week

Posted on 2011-10-04
14
Medium Priority
?
303 Views
Last Modified: 2012-06-27
To all Ms Access and SQL server2005 express gurus,
I need to be able to convert dates to weeks.  I have tried to use { fn WEEK(TopLvSchDate) } and it will display weeks from 0-53...Is there a way to get the correct week based upon a date?

Please help if possible

Thanks
SqlServer.png
dbo.qry-TopLvSchedDate---WeekNum.xlsx
0
Comment
Question by:BajanPaul
  • 7
  • 5
  • 2
14 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 36913278
What do you mean by 'the correct week'?  What format are you looking for in a 'correct week'?
0
 

Author Comment

by:BajanPaul
ID: 36913287
I want the dates to line up with the correct week.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36913327
In Access it would be:

SELECT TopLvSchDate, Format(TopLvSchDate,"ww") AS [Week] FROM AllOrders ORDER BY TopLvSchDate;
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:BajanPaul
ID: 36913344
ADO error: Format is not a recognized function name
0
 

Author Comment

by:BajanPaul
ID: 36913348
Does that work with SQL server express 2005?
0
 

Author Comment

by:BajanPaul
ID: 36913383
I am looking at a few of my dates in December and I believe they should be in week 52.  I was hoping to get the function or calculation to ensure my dates/time fall coincide with the correct week.

27-Dec-11      53
28-Dec-11      53
29-Dec-11      53
http://www.calendar-365.com/2011-calendar.html
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36913448
What I provided was an Access solution.  I see now from re-reading the question you are using an Access 2007 ADP running with a SQL Server table.  Given your solution provides the numbers 0-53 representing the week of the year for a given date, what are you expecting?  The xlsx file shows that.  Are you saying you need the SQL Server query to provide the recordset as shown in the xlsx file? If so:

SELECT TopLvSchDate, DatePart(wk, TopLvSchDate) AS Week FROM [ccc\sarjeantpk].AllOrders
ORDER BY TopLvSchDate
0
 

Author Comment

by:BajanPaul
ID: 36913538
what I have currently that I have tried to use is { fn WEEK(TopLvSchDate) } .  The attached excel sheet is the data set I exported out of access.  i just want my dates to line up with the corresponding week.

I just used your
SELECT TopLvSchDate, DatePart(wk, TopLvSchDate) AS Week FROM [ccc\sarjeantpk].AllOrders
ORDER BY TopLvSchDate
and it is providing me the same data set.  
I want these dates in week 52, not 53.
27-Dec-11      53
28-Dec-11      53
29-Dec-11      53
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36914100
Are all the other weeks correct?
0
 

Author Comment

by:BajanPaul
ID: 36916508
To be honest, I have not matched the days with the weeks to see if they do.  I will do so shortly and post.

0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 36916809
This is not an easy one, as you request the ISO 8601 week number.
Here is a function that will do:

dbo.udf_GetISOWeekdayNumberFromDate

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36920232
If you say that #2011-01-01# falls in week 1, then a simple count will show you that 25-31 Dec 2011 will fall in week 53.  If you say that four or more days of Jan will constitute week 1, then 1 Jan 2011 will fall in week 52.  You will get this problem when 1 Jan fall on a Saturday in a normal year or on a Friday in a leap year - assuming your first day of the week is Sunday.   IOW, the system is not making a mistake.  
0
 

Author Closing Comment

by:BajanPaul
ID: 36926154
Thanks for the link.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36932416
You are welcome!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question