Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.:D


View Replies !   View Related
When The Last Week Of The Year And The First Week Of The Next, Are The Same!
I have the following expression which works nicely to group all of my dates into their respective weeks of the year, by week dates beginning Mondays:

WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.

Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.

How can I adjust my code to get one total group of dates for the week of 12/26/05?

Help greatly-needed!....

View Replies !   View Related
Leading Zero In Week Of Year
I am using the format below to give me Year-WkNo and the leading zeros are not being put in so when a report is ran the yyyy-ww is not sorted correctly. Is there a way to get Jan 2007 week 1 to come out 2007-01?

YearMonth: Format([F Jobs Stats_sub2].[MaxofWork_Date],"yyyy-ww")


View Replies !   View Related
Sort Date Formatted As Year And Week
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.

How can I get Access to sort in ascending order correctly on the formatted date?


View Replies !   View Related
Calcute Week Numbers Starting From 1 July Of Current Year
Hi everyone,

I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.

The starting day of the week is Wednesday.

I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.

I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.

So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.

Not quite working yet. Here's what I've come up with so far.

WeekNumberCount: DatePart("ww",[DateCalled],4)

I use the following to calculate the "my" starting week of the year:

StartingWeekInYear: DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)

Then I add the two together together:

MyWeekNumberCount: DatePart("ww",[DateCalled],4)+DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)

I know it needs some work to be useful for all years. Any suggestions?

View Replies !   View Related
Combobox List A Year Of Saturday Dates
I have a form and I need a combobox on the form to produce a list of Dates for every Saturday starting from the current day displayed in a short format for the user to select. Does anyone have an idea how I could manage that?

I was going to make a table and write in each one and use that for the rowsource, but someone said that there might be a way to do that dynamically.

View Replies !   View Related
Year To Date Totals, Month To Date, Week To Date
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View Replies !   View Related
HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.

For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006

THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.

What do you suggest? Many thanks.


View Replies !   View Related
Access Query Will Not List Certain Date
I have a query that reports on records according to date. The criterea line of the date field has [Enter Date]. I have confirmed that there are dates from 8/1/06 through 8/10/06 in the table, but when I enter the date 8/10/06 the query returns no records. But it does return records for all other dates (8/1/06 through 8/9/06 inclusive).
So then I tried to make the date a range:

Between [Enter Start Date] And [Enter End Date].

I entered 8/9/06 and 8/11/06 in these fields and there in the report were the records for 8/10/06. Then I went back to [Enter Date] as criterea and entered 8/10/06 and no records are returned.

View Replies !   View Related
Week Commencing Day From Week Number
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.


View Replies !   View Related
Query By Week Beginning
I am trying to generate a Query by week always beginning on a Monday. I pick any date....and want all records in both printed version and chart version i,e, number of safety meetings attended vs number who should have attended for this week. I can enter data with todays date, well any day this week. Then at some future date ask for who attended this week and percentages (another story). Any ideas?


View Replies !   View Related
Percentage Of Year Employed - Prior Year
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.

This is being done in a query and we're using Access 2000.

Any help would be greatly appreciated.


View Replies !   View Related
Can You Query By Week Number Rather Than Between Dates
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 !   View Related
How Do I Group Query Results By Week?
Hi everyone

I need to run a query that returns the number of bookings made for each week so far this year. However the date is held in DD/MM/YY format. I had a similar query that worked fine for monthly grouping using Month([Start Date])AS FilterMonth but tried replacing month with week, this didn't work.

Any ideas?

Thanks! :)

View Replies !   View Related
Customers Who Have Not Ordered During The Last Week Query
Hello all, I'm new to this forum.

I'm having problems creating a query for a database (which I am building as part of my university assignment).

The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.

I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.

In case it is needed here is a list of fields in each of my tables:

Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode

Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode

Order ID (CK)
Product ID (CK)
Quantity Ordered

Product ID (PK)
Product Description
Product Cost

Any help would be appriciated. Thanks for reading.

View Replies !   View Related
Fiscal Week And Payperiod Query
I have a database that tracks call-monitoring scores for call center employees.
They are given a bonus based on their scores which is calculated every pay-period.
The following attachment is what I need the query to show. Or perhaps an excel document linked to the database.
Just wondering if you all could take a look at this and tell me what the easiest method would be.
Also, what would be involved.
My major concern is the query being able to differentiate the different fiscal weeks and or payperiods.

View Replies !   View Related
Current Week Query In 2 Date Feilds
I have a query with below formular which return's all records between Sunday and Saturday

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week.
I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time.
What am I doing wrong ???


View Replies !   View Related
Record Missing Using Work Week Query
I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

View Replies !   View Related
Correct Week Number Format In A Query
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 Replies !   View Related
YEAR Query
Please could someone advise me how to set the YEAR in a query - all my data for 2006 is fine but I have uploaded some data for today (01 Jan 07) and ALL by queries / reports now show to 31/12/2006 then at the top, instead of the bottom, reads 01/01/06 ?



View Replies !   View Related
Query On Year Only
I would like to run a query to return all dates for a year like 2007. I need to ignore the day and month so the user will be asked to enter the year 2007 they require a form list on.

I have used the between date query:- Between [Start Date] And [End Date], and its okay but it would be much faster if only one parameter can be typed to return a list of the full year.

Any help would be appreciated, I cant find anything here which helps.

View Replies !   View Related
Parameter Query For Year
Hello Everyone

Instead of having a parameter query ask beginning date and ending date I'd like it to just ask for the year and then have my query display records for that year.

Is there a simple way to do this?

View Replies !   View Related

Copyright 2005-08, All rights reserved