Current Week Query In 2 Date Feilds
Nov 17, 2006
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 ???
Tks
View Replies
ADVERTISEMENT
Jul 5, 2012
Having a bit a brain freeze today. I have a field that auotmatically puts a date in when a checkbox is checked I am now trying to add a field which returns the day of the week from this date. Brain now mashed I am sure it's pretty simple but I just can't get it to work
View 5 Replies
View Related
Dec 3, 2014
How can i make a conditional report that can give me some data from the table.
View 1 Replies
View Related
Jan 23, 2012
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)#
View 4 Replies
View Related
Nov 4, 2006
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
View 3 Replies
View Related
May 31, 2007
Hi
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
Thanks
Daniel
View 6 Replies
View Related
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
Aug 21, 2005
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.
View 2 Replies
View Related
Jan 26, 2006
I'm a bit new at all this but am trying to produce a query that filters on dates within the current week. Is there any way of doing this? I now how to do it for the day but at the moment I'm having to manually input 2 dates to get data in between them. Is there a command that will automatically detect the week and pull up dates within that?
View 8 Replies
View Related
Dec 26, 2013
Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.
DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).
So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.
Office1 5
Office2 1
Office2 1
Office2 1
Office2 1
Office2 1
Office3 5
View 2 Replies
View Related
Feb 10, 2015
Given a specific week no. for the current year... I need to return the first date of that week....
For example : week4 of this year (returns) 19/1/2015
View 2 Replies
View Related
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
Jan 25, 2008
Hi all,
I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.
Here is what happens.
I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.
I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.
I will include the union query here in case.
The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.
' ----------------------------------------------------------
Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry
Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String
Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")
strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value
strFilter = makeHostFilter(strStatus, strReboot)
schFeilds = Split(getRSchechuleFeild(), ",")
sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"
qd_window.sql = sql_window
sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"
qd_nowindow.sql = sql_nowindow
strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)
Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing
makeMainPanelQry = strSQL
Exit_makeMainPanelQry:
Exit Function
Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry
End Function
' ----------------------------------------------------------
I woudl really appreciate any assitance as its my last hope for a solution.
Thanks and regards
Sep
View 2 Replies
View Related
Feb 13, 2007
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 3 Replies
View Related
Jan 3, 2015
For 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()
View 8 Replies
View Related
Feb 26, 2015
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
View 14 Replies
View Related
Jul 29, 2013
Form info
1st Form Name: Mainform
Text to enter employee ID : empid
Sub form within Mainform: SFTimesheet (continues form)
text to enter: Timein, Break, Timeout
SFTimesheet shows and accept entries for each employee called using the text empid, on the mainform
I wanted the subform to show 7 days /lines starting from Sunday to Saturday of current week and dating the text "Timein" accordingly. When employee enteres an entry on the 7th Line, it should give them a new 7 lines for the new week to begin.
View 5 Replies
View Related
Oct 9, 2005
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 9 Replies
View Related
Feb 28, 2006
Hi there, I am trying to put a query on my form which selects the most recent date of a members attendance from an attendance table and displays it in a text box, the code I am using is:
Private Sub Text41_BeforeUpdate(Cancel As Integer)
SELECT MAX(Attendance.AttendDate) as "LastAttended"
FROM Attendance
WHERE Attendance.membershipNo = Forms!Customer.sbfAttendance.Form.membershipNo
End Sub
Customer is my main form and Attendance is my subform. I think the mistake is that I dont have a field called last attended (you are not supposed to store data like this are you) and I need it to display in my text box (text41) on the main form
Thanks in advance,
Jon
View 3 Replies
View Related
Sep 18, 2007
Maybe a simple question, but i do not know how to put the current date in the WHERE clause of a SQL query.
The query i have so far:
select [table1].*, [table2].* from [table1], [table2] where [table1].[field] = [table2].[field] and [table1].[enddate] is null
After this there has to be something like
and [table1].[begindate] < [Date] (where [date] is the expression)
I know that with Date() or Now() the expression can be made, but i do not know how, please advise..
Thanks in advance
View 4 Replies
View Related
Nov 28, 2006
Guys,
I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.
How do I tell a query that I want to display the current date, and then calculate this?
View 1 Replies
View Related
Aug 27, 2013
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
View 1 Replies
View Related
Jul 11, 2013
I am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.
View 1 Replies
View Related
Feb 26, 2014
I am trying to query my records by the current records selected date then send the results in the body of an email on click. I believe I am close but I think there is a problem with the date format because I am getting 3421 Data type conversion error. Here is what I have:
Code:
Private Sub eMail_Click()
On Error GoTo EH
Dim dbExceptions As Database
Dim rstExceptions As Recordset
Dim dbDate As Database
Dim rstDate As Recordset
[code]...
View 3 Replies
View Related
Aug 8, 2005
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
View 9 Replies
View Related
Mar 29, 2007
Ok,
I am making a database which has a set of lessons which have been booked for a particular date.
What I want to do is be able to have a pop-up box ask you for a date for the start of the week. e.g. I want to find lessons from the week starting 02/03/2007 for 1 week (e.g. 2nd - 9th), so I type in the box "02/03/2007" and it comes up with records from that week.
I have tried this in the criteria box:
[Enter date]+7
and
=Date([Enter Date])+7
But nothing works. Also, I decided to add a record with todays date, and then use the criteria =Date(), but that didnt work either!!!!
Help would be greatly apprecated :p
Thanks
Michael
View 3 Replies
View Related
