Hi, I am doing a project at school and need to run a query that displays everyone that has a date of birth within 7 days using a date in the format 01/01/2001. I really have no idea how to do this and would really appreciate any help.
Thank you very much
Kris
Hi all,
I dont know much about access forms - but I have a table that contains all of the members on my site, and the date they joined. Each member can be seen as a sale on my product, so at the moment Im using a simple graph (made using a wizard - shame on me) that just drags out the DateJoined values from the members table.
The result is a bar chart indicating how many sales I made on each day.
Its getting a bit big, and I want to know how I can get it to only drag out last week's sales. I think it will have something to do with the 'Row Source' property of the graph. At the moment its value is...
Code:SELECT (Format([DateJoined],"DDDDD")),Count(*) AS [Count] FROM [tblMembers] GROUP BY (Int([DateJoined])),(Format([DateJoined],"DDDDD"));
Thanks all!
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
I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
I have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.
So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.
So I tried this and got nowhere:
Between #(Date())# AND #(Date()+7)#
I am trying to create a calculated field in my table, I am trying to use the following code:
DatePart("ww",[Gas]![Date Opened])
I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."
Access 2007 - I really need to be able to display the week number in the table, based on the Date Opened field.
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
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?
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!....
What I would like to do is get a count of the product sold and view it over a ten week period in this case week one starts 26th June.
Wk1, Wk2, Wk3 ....
x 10 5 15 etc
y 20 4 12
in the format above but I am not sure how to achieve this, I have the following:
SELECT COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description, Count(COURSEBK.[COURSE-DSName]) AS [CountOfCOURSE-DSName]
FROM COURSEBK
GROUP BY COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description
HAVING (((COURSEBK.From)>=#6/26/2005# And (COURSEBK.From)<=DateAdd("ww",+10,"26/06/05")))
ORDER BY COURSEBK.From;
(the above is working despite access changing the date format between the hashes)
regards in advance foir guidance
How do you convert an inputted date to a week number in a query please.
Thanks in advance. :)
I have a table that has a start day and end day. I would like to get a total for all events occuring on the days of the week (mon, tues, etc.). The problem I am having is when the start day and end day are not the same I need to count that event for all the days in between also.
Example
(Start Day)/(End Day)
(Sat 1/1/2005)/(Sat 1/1/2005)
(Sat 1/1/2005)/(Sun 1/2/2005)
(Sat 1/1/2005)/(Sat 1/8/2005)
(Sun 1/2/2005)/(Mon 1/3/2005)
(Mon 1/3/2005)/(Mon 1/17/2005)
(Thurs 1/6/2005)/(Mon 1/10/2005)
(Thurs 1/6/2005)/(Thurs 1/6/2005)
Results:
Day/num events
Sun/5
Mon/3
Tue/2
Wed/2
Thu/4
Fri/3
Sat/5
Any help you can provide would be greatly appreciated.
Does anyone know a query expression that would always return the date of the first day of the week (a Monday) for the week after next?:confused:
View 2 Replies View Relatedi would like to querry a date which only showing work week and year... i try in FORMAT([MYDATE], 'WW YYYY', 1, 2)... it work but when go to 31/12/2006 it showing "01 2006"... but suppose it should show "01 2007" how can i overcome this?
View 2 Replies View RelatedI have a "To Do" form that I've created using various DataAdd code to advance DueDates annually, monthly, weekly, etc.
Problem is I have a task that is due the second Monday of every month. I've been using If Statements but can't seem to get this to work since the second Monday isn't a even number. It could be different depending on how many days of the month there is.
Thanks,
ChiefRetired
I am new to Access and I am trying to use a chart in a report to display the injuries by day of week. Sunday thru Saturday. I am using a qry to pull the information from a tbl that we have entered the information into. I would like to use a Column Chart.
The problem is that it will display the day of week alphabetically and not Sunday -Saturday. I would also like the chart to display each day even thou there were no injuries for that specific day.
Any help would be greatly appreciated.
I am currently using the calendar provided in Access 2003 in my project. Is there a way to define the first day of the week for the calendar using codes. Any help would be greatly appreciated.
View 2 Replies View RelatedI have table that is used to collect roster information the date field is formatted dddd/dd/mm/yyyy a second field requires the end user to insert W to designate the entry as a weekend date. The end user's often forget to insert the W (not functional) I need a way to code the field to look @ date field and update itself with a W if [DateField] is a weekend date.
A query calculates the roster hours and * the hours field by 1.5 if the W is true. The query works fine, I just want to automate the "W"
Weekday Fn perhaps? Help anybody???
Hi,
I'm looking for a module wich converts a date to a week.
Ex: i type 08/02/2005 and i obtain Week #2
(Some years have 52 and other 53 weeks, depend on the date of the 1st week of the year, an other problem is that some people say the week begins the monday, other say it begins a sunday)
Maybe a built in fonction exists.
Thanks in advance for help.
VINCENT
Hi
I have a client whose working week runs from 0700h on Monday. They do a lot of overnight stuff and so have this cutoff. I am doing a time sheet db for them. All the time sheet records have a starting date and time, and an ending date and time. The end date defaults to the start date but can be updated if an overnight shift. I separated the date and time since I remember issues using the general date format.
I am puzzling over how best to organise my queries so that I can pull up weekly reports that tie in with their week criteria.
Do I build my own table with the date for Mondays over the next few years or is there some smarter way to identify Mondays and then say I want everything between 0700h on that Monday and 0659h the following Monday?
Any thoughts or pointers would be most appreciated.
Thanks and best wishes
I need a formula that will return the nth full week of the month that specified date falls in.
Example:
03/31/2006 would be "5" - the fifth full week of March
20/13/2006 would be "2 - the second full week of February
I've tried using a formula that subtracts the week number for the 1st of the month from the week number the date falls into, but it's just not working as I expect it to.
format([ETA],"ww",1) - format(date(format([ETA],"m",1) & "/01/" & format([ETA],"yy",1),"ww",1)
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
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?
Nav4
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,
Want to use criteria in a date field to query data a week at a time. Open the query and have it ask for week starting MM/DD.
Date input will normally use the Monday of each week and expect data back for Monday through Sunday
Hi I am looking for a formula for my query which filters out only the orders raised in the current week.(5day week Monday to Friday) I did not want to enter dates manualy.
Can this be done ?
Can anyone help me with this.
Tks