In my query im doing the following to pull all records for the previous week
Total Completed: Sum(IIf([Date Completed] Between Date()-(6+Weekday(Date(),2)) And Date()-Weekday(Date(),2),1,0))
What i need now, is one for just Last monday, last tuesday last wednesday last thursday last friday
How to get started with just records for last monday?
Today is 13 May and this code is supposed to return the date of next Monday (19 May)
IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))
but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)
My begin week is Sunday (1) ,
is their a way to have a query to only show data on every monday in current month.
Month([datefield])=Month(Now()) And DateAdd("d",7,[datefield])
Just a reminder if you're expecting responses to your question that Monday September 4 is a holiday in the U.S. and Canada.
View 7 Replies View RelatedHow can I find out the date for whichever the date is of this monday. So, if today is 23/03/2006, then it would give 20/03/2006, which was the date of this monday.Thanks for any advice.
View 2 Replies View RelatedHi all,
I have a 'StartDate' field which must always fall on a Monday and is based on a previous date + interval.
If this StartDate falls on a day of the week other than Monday, I need to add the appropriate number of days to bring it to the following Monday.
This can be done using 6 Update queries (one for each day) such as below but is there a neater way of doing all 6 in one go?
Thanks in advance.
UPDATE JobCards
SET JobCards.[StartDate] = [StartDate]+6
WHERE (((Weekday(Weekday([StartDate])))=3));
Hi,
I'm wondering how to set the default value of a combo box to the Monday of the current week (it's grabbing a date field).
Many thanks in advance.
Hi all,
I have some records lying on ms access db, I need to find the first monday and the last friday of the months in db then select and group them .
How will I do that ?
Hello, all;
I think this is probably a simple solution for some of you.
I have a database containing records for which I need to set dates to follow up on.
I would like to ensure that the date that is entered is a business day (So that when I run my follow up report for the day - I don't end up missing items that were inadvertantly entered for a weekend!)
I have hunted around for a similar thread - but have not found anything.
I am thinking that a simple bit of validation could be entered for the field properties.
Any ideas?
Your help is greatly appreciated.
How to set parameters for the date being last monday to last sunday
So
Paramaters = Date
Then I need
ParamatersWeek = the last monday to the last sunday ...
I have 10 forms named
FormA - FormB ------ FormJ
I want to choose the formA and from a combo I want to choose a day so when this day come the chosen form will open automatically in the specified date when I open the database...
For example i choose FormA and for the date i choose 9 or monday
So every 9 of any month the form will open or every monday....
Hi!
I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.
If so, I have a conditional like this in excel:
=IF(weekday(DATE-3)=0;DATE-4;DATE-3)
If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
I have found a calendar database witch I wanna use in one of my own databases.
But.. on the form the weeks start on sunday, I want the week to start on Monday but I can't figure out how to do it.
I've posted the original database as an attachment. I'd like to know what I have to change in the code and/or in the form to get what i want.
I have a textbox in a form. What I want it to do is to display the following weeks Monday date. Sounds simple but requires you to know the Monday dates in advance ...
View 14 Replies View RelatedFor some reason i'm having difficulty getting the Current Monday and Friday of the Current Week on my Main Menu. Monday shows 12/29/2014 ???
Here is the code for the scenario.
Monday: =CDate([AnyDate])-(Weekday(CDate([AnyDate])))-2+4+3-3
Friday: =Date()-(DatePart("w",Date(),2,1)-1)+4
AnyDate: Date()
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I'm trying to show the day in a query "monday or whatever the day s on correspondence to a date in the same query named "SALEDATE".
I have tried using several ways but cant figure it out.. Can someone please help me out here with a boost?
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
View 1 Replies View RelatedI have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.
In my head it should work something like this
1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)
2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.
Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedI have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.
Each record is made up of Airport, Fuel Uplift, Month and Airline. I want to create a query that will show each Airport per month with the total fuel from both airlines. To do this I've created a new field titled AirportMonth and then applied a JOIN command. This works to a point but initially only returns records where the same AirportMonth value appears in both tables. I can alter the join properties to show all records from one of the tables but I need all records from both tables. Below is the SQL I have so far.
SELECT JAFyr.Airport, JAFyr.Month, [JAFyr]![Fuel Uplift USG]+[TFLyr]![Fuel Uplift USG] AS Expr2
FROM JAFyr LEFT JOIN TFLyr ON JAFyr.AirportMonth = TFLyr.AirportMonth;
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
Hey,
I currently have an Access front end backed up by SQL server.
I have 3 subforms that need to use a Access query that is based on a linked SQL table. It does everything I need but let me add new records. Upon checking the linked table has an enabled new rec button but the query doesn't.
Does anyone know what I need to change to get the queries to allow new records to be added since each subform has its own query?
Can I use left and right outer join at a time? Means I want all records from both the table, and it should put 'null' whenever value is absent.
View 1 Replies View RelatedHi All,
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
Thanks in advance:D
Cheers,
Karv