I am trying to find a simple way to determine the work date based on knowing the Start date and how many days ahead I want the date for. These means I need to exclude weekends and holidays.
I tried Pat Hartmans solution located at
[URL]
I discovered that the function in there that is supposed to do this does not account for weekends, only holidays.
In my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
Any help will be appreciated, thanks
Hi,
Is there any access formula that can calculate the net business days between two dates.
Regards,
Jatz
I have a form for the user to enter new units which arrive in our warehouse, one field in DOM (Date of manufacture) which cannot be a date in the future obviously but could be todays' date. there is table level input mask to make the date 00/00/0000.i have this code on the form:
Private Sub dom_BeforeUpdate(Cancel As Integer)
If Me.DOM > Date Then
MsgBox "Please enter a Valid DOM", vbInformation, "Check again !!"
Cancel = True
End If
End Sub
which doesn't accept any date whether it's today, last week, last year or in the future!
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
View 5 Replies View RelatedI'm currently building my first database for a research project. When we enroll a participant, we need to have an enrollment date/time as well as an activation date/time (and they are not allowed to be the same because, rules). I was able to figure out how to auto-populate the current date/time when we've marked a person as enrolled. What I'm wondering is if it is also possible to auto-populate another field with the date/time, ten minutes in the future?
View 3 Replies View RelatedI'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
In a date field is it possible for a date that is in the future to be hi-lighted in a different colour.
E.g. todays date is 25/03/14
So anything with todays date or previous would stay in black
24/03/14
25/03/14
but anything from 26/03/14 forward would be in red
26/03/14
Then will change to black when the 26th comes.
I've two fields to work with:
[Date of Device]
[DischDate]
If i was explaining it, it would be as follows:
If [DischDate] Is in the next month after [Date of Device] then Y else N.
to add for example if the [Date of Device] is April 2015, and the [DischDate] is also April then i'd expect a N answer
to add for example if the [Date of Device] is April 2015, and the [DischDate] is May then i'd expect a Y answer
Could someone please help.
I have to fields in a form for a membership database that I want to relate to each other, 'date joined' and 'expires on'.
I have been trying to build an expression in the 'expires on' field that calculates the date which would be 12 months from the date entered in the 'date joined' field.
Can anyone please point me in the right direction.
Thanks
Adrian
Hi, All...
Can someone tell me how to calculate a future date in a bound field on a form? The form includes "date created", "life expectancy" and "review date" fields, all bound to the same table. I want the "review date" to be calculated by adding the "life expectancy" (number field, in days) to the "date created" field. I found some posts that were similar but none using bound fields.
Thanx so much for you help with this...I've spent quite a lot of time trying to come-up with a solution...to no avail.
Thanx ;<)
Karen
Hi,
I am trying to update a field on a form with a date that is +7 days from a date that is in another field, but the new date must only be a business day. Can anyone tell me how to do this?
Thanks
Is there a function in Access that will allow you to add business days to a date? I want to pull a date with a query and then add 5 business days to it to display in a report. Anyone know if this is possible?
Thanks.
Hey all,
I'm designing a query that must calculate a future date based on a recorded date. Unfortunately it's not as simple as just using adddate (unless I was using Excel!). Here's my requirements:
Future Date = [StartDate] + 45 business days
I am able to take weekends into account, but haven't been able to figure out how to bring holidays into the equation. I have an existing holidays table that I am using for another query within the database (calculates # of business days between 2 existing dates).
Hope this makes sense... I've been staring at it all day so I may not be thinking clearly anymore.
Any help is greatly appreciated.
I am very new to access and I am trying to figure out how to build an expression for one of my fields. I have a field called Order Date, which holds the date of when an order is filled. I then have another field called Fill By Date. This new field I want to be 2 weeks after the Order Date. Is there a way to auto fill this information so that when I enter a date into the Order Date, it will enter the date that is 2 weeks after into the Fill by Date?
View 4 Replies View RelatedI am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
I am dabbling in updating an existing database.Currently our staff enter data via a form and I have this set up using the below script to automatically complete the "Due date" field with a date 5 working days ahead of the current date, saving them from having to manually enter this date each time.
Code:
Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
[code].....
This then updates the value on the relevant form object using the default value "PlusWorkdays(Date(),5)" to give a date 5 working days ahead. the problem I face is that due to our business process at 15:30 each day our due date needs to change to 6 working days from todays date rather than 5. I was wondering if there is a simple way I can modify the script to add an extra working day when the local time hits a specified point (15:30 in this case), unfortunately this is a bit outside my abilities currently!
Querying a field for a number of days from today's date but not had a lot of luck finding how to query this from a date in the future.
Currently I have as criteria for the relevant field:
Code:
<[Period Start (dd/mm/yy - less 5 years from financial year end)]
which prompts for a parameter but using this I have to figure out the date 5 years from the future date first and then enter this.
Surely there must be code that allows me to simply enter the date, in the prompt and this then shows me data of 5 years or older equipment!?
I have created a simple database, with 2 x tables. The first is a 'Week Ending' table, with a dateid key and Week Ending field. I have created several week-ending dates in order for a user to input details of work done the previous week. The second table contains the details of the work.
I have a one to many relationship between the dateid in the first table, and a dateid of the work details table.
I created a query based on the work details table, and created a subform from that. In a main form I linked a combo box to allow a user to select a date and the relevant work details appear in the subform. However the Week Ending field only shows one date, not a list of the dates I created.
I am very new to Access and have virtually no experience with vb, I'm trying to build a customer database with some forms that enable myself and a couple of others to keep track of customer contact.I have designed my form with 5 buttons at the bottom of the page with different future date values, I have made a field in my database called FollowUpBy and i would like to be able to click the buttons and have access take today's date plus the relevant follow up time-scale and insert that into this field. Also but on a separate note, I have the yes/no box for follow up required is there a way to make the buttons at the bottom and the date picker unavailable (perhaps shaded or crossed out) unless this box is checked?
View 1 Replies View RelatedNot sure if this is actually done with a query (hell, im not sure if it can be done) as my Access knowledge is limited but I've got a list of Patient Appointment Dates and I need to calculate a list of 'Next Appointment' dates that are six months on from the appointment dates not including saturdays and sundays (as the health center is closed those days)
BTW this is for coursework, I don't really work for a health center :S
edit: I seem to have put this in the tables forum, not the queries forum. Apologies for that, I'm not an Idiot, Honest!
I have a query in which I have a long list of orders and their entry date and due dates. I would like to calculate the number of working days between the both in order to see if the supplier keeps his theoretical delivery term.
Can some one give me a way to achieve this in my query:
DUE DATE.........ORDER DATE.........DELIVERY
03-03-2007 -/- 24-10-2006 = working days
many thanks in advance
I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies View RelatedHI there,
I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.
I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.
Hoping someone can assist.
Many thanks
Hi all
I have a text box in a form which is displaying the date with a default value : =date()
Now what i want is another text box which will display for example 30 days after of the todays date.
Lats say today is 20/10/2005 the second text box will calculate 30 days after and will show 20/11/2005
Thank you.
Please help me calculate the age of a person at a particular date without having to go into the expression every year and change the cutoff date year.
A basketball player in our league must be a certain age as of Sept. 30 each year to play in leagues divided by age. I am using the following to calculate age:
age: Int(DateDiff("d",[bdate],#9/30/2007#)/365)
I want to get rid of the 2007 and replace it with an expression that will automatically change every year.
Thank you for your time and consideration