Can You Query By Week Number Rather Than Between Dates

May 17, 2005

Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.

View Replies


ADVERTISEMENT

Correct Week Number Format In A Query

Jan 6, 2005

Wondering if anyone can advise...

The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:

WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)

However my problem now is that I need to replicate this format in a Query within Access. The formula

WeekNo: Format(Date(),"ww")

treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?

There's a challenge for someone!! Help appreciated.

View 1 Replies View Related

Queries :: Week Number Query When Year Changes

Feb 28, 2014

I analyse things by the date they are ordered by the office.

The date gets put into a spreadsheet and i import this into access for the querying.

I need to summarise it by weeknumber and year.

I use the following queries:

To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]

The problem is when the year changes.

The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.

Therefore when the year and week number is put together the following results are given for that particular week:

DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1

All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.

How can I amend my query so that the above all show the correct year and week number: 2014_1 ?

View 9 Replies View Related

Week Commencing Day From Week Number

Nov 6, 2006

I have a query that groups records by date into week numbers using the following expression.

WeekNumber: DatePart("ww",[Date],2)

I would prefer to display the result as the week commencing Monday date.

How?

View 13 Replies View Related

Mapping Week Numbers And Dates

Mar 27, 2007

Hello thank you for reading my question

I am setting up a database for a training centre, part of this includes registration
The academic year I am working with runs over 36 weeks with 24 teaching weeks, 2 x 3 week holidays, 2 x 1 week reading weeks and the final 2 weeks for exams.

I aim to set the system up so that when a user opens the application, I get the username from windows, the day and time then open the correct register. That bit should be fine.

As I have 36 weeks I want to some how map the week numbers to the dates at the start of the term and also the holidays. There are 2 reasons for this, the first being so that when the user logs in the register only shows the current week instead of needing to search along all 24 or 36 entries, the other reason is so that when checking the whole register the holidays are not shown.

Does anybody have any suggestions or pointers as to how I can do this?

Thanks

View 2 Replies View Related

Modules & VBA :: Converting Dates Into Week Numbers

Oct 2, 2013

I am trying to write some VBA to convert a date into a week number.

My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:

I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.

Start day for the week is Sunday. End day is the Saturday.

Some examples to check against:

22nd December 2013 = Week 52 2013

29th December 2013 = Week 1 for 2014
----

21st December 2014 = Week 52 for 2014

28th December 2014 = Week 1 for 2015
----

20th December 2015 = Week 52 for 2015

27th December 2015 = Week 1 for 2016
----

25th December 2016 = Week 53 for 2016

1st Jan 2017 = Week 1 for 2017
-----

Tried various methods already

iso 8601, wrong week start day

intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.

View 14 Replies View Related

Number Of Days Between Dates In Query

Apr 22, 2005

I want the number of days between a specific date (roll out date) and 12/31/2005 so that
I can use the number to project revenue for this year.

This is what I'm using but it doesn't give me the right number. I don't know how to transform
something to a datetime variable so maybe that's the reason. Can you help me out?

Expr4: Day(12/31/2005-[Scheduled Roll Out Date]-0.49999)

View 1 Replies View Related

Reports :: Filtering A Report Through Start Of Week Dates

Jun 23, 2015

I've restructured my 'application' to have one table and a lot of query/forms. In light of not figuring out the combo box situation I altered the requirements and what I needed thinking I may have an easier go of it, but alas not exactly my experience. I have 5 reports that I want to filter them by week(start of the week). I have a function which can turn any regular date entered to the start day of the week(monday). I thought my best way about this would be to fill a combo box with all the accepted dates through a separate table's column.

Is there a way to pop a dialog box when the user clicks to generate the report that will prompt them to choose a week then run that criteria against the report and only bring back that date?

View 14 Replies View Related

Modules & VBA :: Insert One Week Dates Ahead Of Last Enter Date?

Jan 13, 2015

I would like to enter a week dates forward after looking up the last entered date. i have working codes that looks up the last date and increments it by one and enter the date into a new field.

Here is what i have:

Private Sub Form_AfterUpdate()
Dim dtmNextDate As Date
Dim dtmLastDate As Date
Dim SDate As Date
LastDate = Nz(DMax("[SDate]", "schedule"), _
DateAdd("d", -1, Date))
NextDate = DateAdd("d", 1, LastDate)
CurrentDb.Execute "INSERT INTO Schedule(SDate) VALUES ('" & NextDate & "');"
End Sub

enter dates into 5 new fields instead of one using a loop maybe.

View 6 Replies View Related

Funktion For The Week Number

Oct 29, 2006

Hi,
I will really appreciate ur help. I work on Access-2000 database and have a problem. I need to get the number of the week. So i used the funktion as a standard value as following:
"=((Monat(Datum())*30,5-Tag(31-Datum()))/7)"
It shows me for today 39 instead of 44.
Could u please help me and tell me why this funktion doesnt work?
Thanks in advance

View 5 Replies View Related

Week Number Problem

Jan 18, 2006

I have a large report that runs a six weeks average based on the last six weeks of production. Our fiscal year starts November 28th and our weeks starts on Monday.

I have used the following format to start the week on Monday

Week1: (DatePart("ww",[EntryDate],2))

To set up the fiscal year I used the following format in my query.

FiscalYear: IIf(Format([EntryDate],"mmdd")>"1127",DatePart("yyyy",[EntryDate])+1,DatePart("yyyy",[EntryDate])) . The criteria is 2006

I have a Weekending table that I have all the weeks listed including the weeks in calendar 2005 that are included in our fiscal 2006 that I have linked to my query.

When I run the report, it will not include weekending 1/1/06 (Week 1). It is totally eliminated. There is probably a conflict in the above formats, but I am not knowledgable enough in Access to figure it out. I have been searching this site without success. I would appreciate any help I can get. Last week I had to do the reports manually because I couldn't get the works to run right.

Thanks,

View 1 Replies View Related

First Date Of A Week Number?

May 14, 2007

Hi Folks,

I use this to get a week number for a date range:

WeekNo: DatePart("ww",[Timestamp])

Is there a way that I could also get just the first date from that week number to appear in the query as well?

ie. if Week 1 is 3/1/07-3/7/07 the query would show 3/1/07 as well as week 1. TIA

View 1 Replies View Related

How To Identify WEEK Number

Feb 8, 2007

hi

i am in a situvation that i have to identify the dates which comes under particular week1 or 14.

View 3 Replies View Related

Queries :: Query Between Dates But With Count Grouped By Model Number?

Mar 18, 2013

I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.

View 14 Replies View Related

Queries :: Calculate Total Number Of Days Between Two Dates In A Query

Feb 18, 2015

Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.

I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.

The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.

This is a formula I tried to piece together but obviously not correct as it's not returning anything;

IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],
Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))

View 2 Replies View Related

Queries :: How To Add Sequential Dates With Reference Number With Append Query

Apr 7, 2015

I want to populate a table with field:date and field ref nr.(7 fixed ProductID's)

But only weekday and to 3 years from now. (every weekday)

Example

field:ID | date | fixed.ref.nr
--------------------------------
1 | 7/4/2015 | 1001
2 | 8/4/2015 | 1002
3 | 9/4/2015 | 1003
4 | 10/4/2015 | 1004
5 | 13/4/2015 | 1005
6 | 14/4/2015 | 1006
7 | 15/4/2015 | 1007
8 | 16/4/2015 | 1001
9 | 17/4/2015 | 1002
10 | 18/4/2015 | 1003
.... (ID field is autonumber field and not important..)

How to do this with a append query or code?

View 1 Replies View Related

Week Number Of The Current Month

May 24, 2005

Hello everyone.

I would like to ask for help on my current problem.

I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.

for example:

May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1
May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2
May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3
May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4
May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5

Same goes for all months.
Is there a function to determine which week number of the current month the input date falls?

Please help.

Thanks,

Edwin

View 2 Replies View Related

Period Based On Week Number

Aug 23, 2005

Hi,

I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.

How would I obtain the start date and the end date from just the week number?

Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.

Any help would be much appreciated.

View 2 Replies View Related

Display Date And Week Number??

Apr 10, 2008

hi

i would like to display the date and week number on one of my forms but have no idea about dates and how to code this.
can some one help? id appreciate it it beyond me :confused:

thanks :)

rob

View 4 Replies View Related

How Can I Convert A Week Number To A Month?

Sep 29, 2005

Help!!
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??

View 1 Replies View Related

Determine Start/end Day For A Week Number

Apr 24, 2008

I am trying to avoid the creation of a lookup table where I know I should be able to calculate this. I need to determine the start and end date of a week number. I did find this post from 2002: http://www.access-programmers.co.uk/forums/showthread.php?t=33116&highlight=start+end+dates+week but it doesn't seem to work.

What I am trying to do is show all projects that were opened at the start of a week number. For example, week 2 is from 1/7/2008 and ends on 1/13/2008 (provided that your week starts in the week that 1/1 occurs and that your week starts on Monday). I need this start date so I can show all projects that started on or before 1/7/2008 and were in an open status.

If I can't calculate this then I will have to create a table and relate the query to it.

|Week Number | Start Date | End Date |

View 3 Replies View Related

Date/week Number Calculation

Feb 3, 2005

Hi,

How can i do the following -
When a date is typed in a text box, ie, 17/01/05
I want a Query to return what Number week in the year it comes from.

In this example the result would be Week "3"

I'm sure this would be easy enough to do if i typed in every day in the year into a table with the
week number beside it.

Is there some code or a more straight forward way.

hope all makes sense, if not i can go into greater detail.

View 2 Replies View Related

Show Date Range Instead Of Week Number

May 1, 2008

When using the Format:'yyyy mm dd' in access queries.
When trying to show a week is there a way to show the
date range instead of week number.

Week 01 = Jan1-7. If the results are only week numbers
that makes it hard for people to understand when it
actually is. Bottom line is: I would like to convert
Week number to actual date range. Can anyone help with
this?

View 9 Replies View Related

Forms :: Filter Records By Week Number

Apr 23, 2013

I have a summary form with the company name,week number, week-ending automatically populated. e.g. this week is week 4 and users enter records for week 4 as the week number is automatically generated. Basically the system will just recognise today's date and generate the week number. Problem is I am trying to display previous week records and I do not know how to filter it as my week number is generated automatically, week by week. All the other forms are linked to this week number. How can I display records for a specific week that I want? say, I want to display all records for week2, etc. I am dumbfounded as I am on a learning curve with Access.

View 2 Replies View Related

Queries :: Counting Number Of IDs For Each Week - GroupBy

Oct 22, 2013

I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.

Code:
SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;

View 2 Replies View Related

Queries :: Unable To Get Current Week Number

Jan 10, 2014

I need to a query on the week number, the week number is in the table. Imjust struggling with getting the current weeks data.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved