Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
I'm trying to create a sub to count how many days between 2 dates excluding Holiday and weekend. Using the DateDiff() function is not allowing me to exclude certain days.
View 2 Replies View RelatedMy subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)
in a text box, and it works fine.
But I don't know the syntax to get these numbers:
the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.
Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))
The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.
They are: the appointment date and its date parts - defined in numbers.
App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)
how to get my weekend work and not work total day count?
I can't get this to work! I am re-using a database to create new reporting. It was a technician utilization database that I am reworking to show weekend days worked. I changed the date format to Long Date to show the day, then started a query. The query is looking for *Saturday* but pulls nothing in the date field that I just changed to Long Date. What am I missing?
View 2 Replies View RelatedI created a database for an organization I volunteer for but I am running into design problems. I want the database to be able to assign preferred days of the week (that the volunteers want to serve) with the flexibility to change them.
The problem is in order to be able to keep my reporting options flexible. I will have to write a record (on a separate table) for each volunteer for every day they want to serve. A one to many relationship between the volunteer table and preferred days table.
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
Hello everyone,
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer
dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Else
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday
strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"
'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates
If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If
Else
Sunday.Value = 0
End If
DayofWeek.Value = Format(dteToday, "dddd")
End Sub
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.
Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
I have a query result for example = 2.19 days worked. The result of cumulated hours divided by 8 (hours in the workday). What I want to do is take the .19 and multiply it by 8 (hours in the workday) and I would have the result in actual hours which is what I need. Or of course if there is a way to display that into 2 days and 1.52 hours Rounded up to quarters would be even better. But with spending hours looking through forums I didn't find a way to just work with the decimals right of the dot. Trim, format, Len didn't work for me, they all give me funny result.
View 7 Replies View RelatedI rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
your help would be much appreciated
merry christmas to you all
Jabez
How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?
View 4 Replies View RelatedDear All,
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
'xxxxxxxxxxxxxxxxxxxxxxxx
Can someone please direct me to the right path.
Your help is highly appreciated.
qwerty70
I have a database to monitor incoming emails from our contractor & my superior wants it to have control like the following situations:
1. If no respond yet, to calculate today's date minus received date
2. If responded, to calculate respond date minus received date
If i use the following expression, it does not relate to if the emails have no respond yet or have been responded.
IIf(IsNull([Reply Date]),DateDiff("d",[Due Date], Now()))
How to amend the below, so that instead of doing 4 days, it does 4 working days?
<=DateAdd("d",4,Date())
I am looking to do a calculated field. Not sure what is the best way to do this, but I would like to have a dates in a table with all of our employees that has what date they started with the company. From that I want to know not how many days they have been with the company, but just the years.
So if I have been with the company 364 days, it should only show up as 0, and if I have been with the company for 365, it will read 1. I would like this to be in a query somehow. We use the number of years they have been with the company for their pay, and need it to be years, and not days.
How can I make that my access send an email to a user if there 7 days until en employee's birthday.
View 5 Replies View RelatedI'm very new to access, but see it has potential for a big payoff for a project I am doing. Currently, I'm stuck summing across the columns for Step 1 EC (est completion) - Step 8 EC. This may be a poor design on my part from inputting the data in the table. But all of these jobs being a stochastic process with always different steps in machining, I don't know how else to do it.
With all that being said, I would like each Piece Part to have a sum of days at the bottom of estimated completion and actual completion. Again, it must show the total for EACH part.
For example, as shown in the access file in report under "Piece Parts Report" the first part (No. 2 Aluminum Base Plate), it goes through 3 steps... with the steps being 1, 5, and 5 days respectively. I'd like for it to show at the bottom a total of 11 days for that part to be manufactured.
I have a Date field [TESTDATE]
I would like a query that shows the last 45 worth of data entered into the system. How would I do this? Thanks.
Travis
Hi
I have a table which has a start date and an end date on it: e.g. 25/10/06 to 3/11/06.
I want to set up a query to calculate the number of days between those two dates, but only for the month of October: so it would only come to 7 days (not 10 days).
How do I do this please??
Also: once I have the formula worked out, how can I write it so that a user can enter the month they want to query (e.g. october)?
Thanks
Maria
how do i make a query that only shows things with a date in them of only the previous 7 days?
this is urgent due to needing submition in 3 hours time
trying to think my way around this one. i am in search of a query that will allow users to see what they have ordered within the last week (7days max) which starts on monday of each week eg if they run the query on thurs, they will see mon, tues, weds & thurs and not just 7 days from thurs [Date()-7]
i'm assuminging its gonna be something like if day=monday then query will = date() else if day=tuesday then query will = date()-1 and so on.....