Future Date - Taking W/e & Holidays Into Account
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.
View Complete Forum Thread with Replies
Related Forum Messages:
Expression For Future Date
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
View Replies !
Calculate Future Date
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
View Replies !
Taking One Date From Another To Give Number Of Days Left
HI, this should be relatively simple, but for some reason I cannot figure it out. I have a field titled "DEADLINE" - a user inputs a date in this field (the datatype for this filed id date/time) What I want to do is, on the form create a text box which takes the deadline date - todays date to give me the number of days to the deadline date. Once the dealine date has passed I then want to turn the counter to Red to show it is overdue. If there is no dealine then I want the field to say something like "No Deadline Set"
View Replies !
Accumulating Holidays
OK Here's the deal: An employee has 0 holiday days in the first year of employment and accumulates 1 holiday day for every 1.2 months worked after the first year of employment. So after 2 years of working the employee is entitled to 10 holiday days, but if the employee only takes 5 holiday days they should be able to accumulate the rest of thier days into their next year. I've added the database that I've created so far, I haven't got too far, and I realize that I will have to create a field in the employee table with the first day of employment. Any help with this would be greatly appreciated.
View Replies !
Staff Holidays
Hi All, I need a query to tell me how many staff are on holiday on the same day..here what i have so far. A query with name, startholsdate,endholsdate, totaldays, etc, etc,, records showing; employee A books 01/01/06 to 14/01/06 and employee B books 10/01/06 to 24/01/06 C books 01/02/06 to 10/02/06 and so on, I need to show how many employees are off on what days eg employee A and B are on hols between 10/01/06 and 14/01/06, so only 3 more staff may take days off between the 10th and 14th. I have tried all sorts of calculations but to no avail? can anyone help.. thanks
View Replies !
Employee Absense/Holidays Database
Hi all, I am stuck where to start and wanted some advise. I want to create a database that captures whether employees are late, sick, holidays etc. So i would have a list of employees, then create a new records in another table to store what type of reason it is, (late, sick, holidays etc.), and then the directors can monitor employee sickness. But what i would to do is to be a bit clever about it though in 2 ways. Against the employee they would have a number of allocated holidays against them, but i just wanted the user to enter the start and end date, and then get access to work out how many holidays days need to be taken off, i.e. it does not included weekends or bank holidays.... Also in the sickness report, work out how many single days where taken on either a Monday or Friday.... Has anyone done or seen an access database that can do this already. I know there is a calendar addin, but i've not used it...
View Replies !
Bank Holidays: Advice Needed
hi i'm trying to build something that knows when to notify someone that they can call a trade, given 1) a callable date 2) a notice period and 3) the relevant cities e.g. ?NotificationDate(#28-jun-2008#,10,"LONY ") 16-06-08 the good news is, i've done it, with this code:- '--------------------------------------------------------------------------------------- ' Procedure : NoficationDate ' Date : 28/03/08 ' Purpose : to calculate the date of notification for an EMTN, given the call date & notice period & cities '--------------------------------------------------------------------------------------- ' Public Function NotificationDate(dtCall As Date, intPeriod As Integer, strSixDigitCities As String) As Date Dim intWorkingDaysBefore As Integer Dim strCities(2) As String Dim dtLoop As Date strCities(0) = Left(strSixDigitCities, 2) strCities(1) = Mid(strSixDigitCities, 3, 2) strCities(2) = Mid(strSixDigitCities, 5, 2) dtLoop = dtCall intWorkingDaysBefore = 0 Do dtLoop = dtLoop - 1 If Left(Format(dtLoop, "ddd"), 1) <> "s" And IsBankHoliday(dtLoop, strCities(0)) = False _ And IsBankHoliday(dtLoop, strCities(1)) = False And IsBankHoliday(dtLoop, strCities(0)) = False Then intWorkingDaysBefore = intWorkingDaysBefore + 1 End If Loop Until intWorkingDaysBefore = intPeriod NotificationDate = dtLoop End Function '--------------------------------------------------------------------------------------- ' Procedure : IsBankHoliday ' Date : 28/03/08 ' Purpose : to see if it's a bank holiday '--------------------------------------------------------------------------------------- ' Public Function IsBankHoliday(dtInput As Date, strCity As String) As Boolean Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_Tass_All_Hols WHERE CITY = '" & strCity & "' AND HDATE=#" & Format(dtInput, "mm/dd/yyyy") & "#", dbReadOnly) If rs.RecordCount > 0 Then IsBankHoliday = True Else IsBankHoliday = False End If rs.Close Set rs = Nothing End Function BUT it runs like arthritic toad, it makes a minute per execution and i was hoping to scale it up to 4000 records => 2 days of run time :eek: any ideas on how to attack this problem...even guesses appreciated, i can try things out and see if they work thanks in advance
View Replies !
Future Version Of Access
When will Access support the .Net framework? Will Microsoft ever consider making another RAD program similar to Access that supports the .Net framework? Just think of an IDE of a RAD program similar to Access that has a full set of toolbox items already built in .Net that supports all of the functions / options that Access currently supports in its forms, reporting etc. and have the ability to interface with all of the .Net database engines... :cool:
View Replies !
Calculate Future Dates
Help Help Help - I have a database which I need to calculate a date 3 years from now, then report on it 6 months before the 3 years expires... Hope that makes sense Fay1136:confused:
View Replies !
Please Help Structuring Db For Future Expansion
first thing's first... i've never set up a Access db. I have, however, worked with php/mysql so I understand dbs and the basic principles behind them. i was hoping you could help me set up my db to accomplish my goals now and help me in the future when i want to make this system more advanced. the purpose of the database is to track the 'stages' of several different projects as well as keep account of which contacts are involved. example I have projects A, B, C I have contacts I, II, III the stages of the projects are 1, 2, 3 I already have these tables (projects, contaces, stages) the first issue is, i dont know how to assign mutltiple contacts for specific projects i.e. project A involves I, II, and III while project B only involves I and III. I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project the second issue... i would like to db to store dates of when a specific project moves from stage 1 to stage 2 for instance. i know i can set up fields in the projects table for these, but what happens when i add a new stage? will i need to go into the projects table and add a new field? can i make this dynamic? i hope this makes sense to you. if you could give me direction, i would greatly appreciate it. before doing something that i will regret later, i hope you can give me some tips based on your experiences. thanks in advance.
View Replies !
Finding Dates In The Future
I have a table that holds the info on our fleet of company vehicles. One of the fields in the table is called ServiceDueDate. I want to be able to set up a query that shows any vehicles that have a service due in the next 30 days. I know I'll need to use DLookup in there as well as Date - 30 but not sure exactly how to write the query. Any help would be greatly appreciated.
View Replies !
Query By Future Paydays
I have a table of checking transactions. Some are past, some from today, and some are dated with future dates (such as recurring payments). Is there a way to set up a query that will exclude transactions that occur after upcoming paydays (1st and 15th) dynamically? For instance, if I have a transaction scheduled for the 2nd of next month (after payday on the 1st) I'd like to be able to separate it from the transactions that are coming out of this payday. Thanks for your help.
View Replies !
ASP, Access, And Future Dates
I'd like to create a recordset from an Access database that reads the 'Date' field and only selects dates in the past. That way I can enter info ahead of time, give it a date of a week from now, and know it won't show up on my pages until next week. It should be simple, but what I have isn't working. Here is my code: Query = "SELECT Id, Date, Info FROM OldNews WHERE DateDiff('d', Date, date()) <= 365 ORDER BY Date DESC" It lists all the records that are 365 days old, but it still shows any future dates as well. Does anyone have a better idea? Thanks!
View Replies !
Project Future Cash Flows
I have been asked to build a query to project cashflows for leases. SELECT Tbl_Portfolio.TERM_DATE, Tbl_Portfolio.COMM_DATE, Tbl_Portfolio.PAYMENT, Tbl_Portfolio.Contract_Term, DateAdd("m",nz([contract_term]+6),[comm_Date]) AS MaxBill, DateValue("July 1, 2006") AS SDate, IIf([Sdate]<[TERM_DATE],IIf([sdate]<[MaxBill],[PAYMENT],0),0) AS 1stDate FROM Tbl_Portfolio; It calculates the maximum Bill date as 6 Month beyond the end of term. Then It sets a Start Date "SDate" as 7/1/2006. It then Looks at the "SDate" in this case, and if the date falls into a range where it is valid it put the payment in. Ideally I need it to carry this forward for the following months, something Like SDate + 1Month, then SDate +2 months and so on. i.e. IIf([Sdate +1month]<[TERM_DATE],IIf([sdate+1month]<[MaxBill],[PAYMENT],0),0) One of the problems is this begins to get to be pretty large and laborious when you are talking about 120 month leases. I sort of have this working on a form attached in the Ereports database, but would prefer to see if I can do it easier. Any sugestions would be appreciated. Ideally producing output that looks something the attachment CLFOW Thanks Bruce I am probably not explaining my self well, Let me try again. I have the following data: Contract St_date End_date Con_term Payment Max_Bill_Date 12345 1/1/2006 1/1/2009 36months $250.00 6/1/2009 Maybe its a pivot table, But what I want is project out the Payment on a month basis. Aug 2006 Sept 06 Oct 06 ..... June 09 Contract 12345 $250 $250 $250 $0 So the query Checks to see if the Column Heading Date is with in the acceptable date range of the contract, and if so puts the payment in the field.
View Replies !
Link To Future Spreadsheet Tabs
It's easy enough to link to someone else's Excel spreadsheet and get their data into my Access database but I have to do it each month. They create a new tab each month (OCT-2007,NOV-2007,DEC-2007) for the month and the spreadsheets always have the same structure. Is there anyway to tell my database to go out and look to see if the new spreadsheet has been created and if it has then import the data?
View Replies !
Query Based On Future Paydays
I have a table of checking transactions. Some are past, some from today, and some are dated with future dates (such as recurring payments). Is there a way to set up a query that will exclude transactions that occur after upcoming paydays (1st and 15th) dynamically? For instance, if I have a transaction scheduled for the 2nd of next month (after payday on the 1st) I'd like to be able to separate it from the transactions that are coming out of this payday. Thanks for your help.
View Replies !
Future Modifications To BackEnd Database
I've created a database to track my client information. I've split it into front end and back end databases. Everything there works fine so far. I have other co-workers who track their own clients who I will be giving this database to. So we will soon have the same database with completely different data stored in them. If I want to make changes in the future, I should easily be able to change the front end, get them a copy to install, and all should be well. But what if I want to make changes to the backend? Like if I decide I want to track some completely new data in a separate table on the backend. I won't have the ability to just go out to the central server and change 1 backend database. There may be 10 identically structured backends on 10 different machines that all need the new table added to them, without effecting their unique data in the existing tables. So, assuming my explaination made sense, is there a way to make backend changes like I'll need to? Or would I have to go to each backend database and manually make the changes?
View Replies !
Converting From Access 2003 To Future Versions
I've been hired to clean up and convert a flat filemaker database into access 2003 and I've spent the last two weeks learning the ins and outs of access and doing preparations. Well, today some high up database guy in another department told me not to use access because in the future when a new version of access comes out we will have to recreate the database from scratch because there are lots of problems converting between versions. I'm aware there might be some hiccups but I don't really agree with him. Plus once the database is up and functioning I don't see why we'd even have to upgrade it to a new version. It's just a small health centre patient database with info about patients and their treatments. Does anyone have any opinions about this. Thanks.
View Replies !
Clash In Existing Data And Input In Future
Hi everybody, There is a chance that I might be overlooking a simple way to solve this, so any suggestions I would appreciate. I'm working on a database and since it's my first database, I did not follow the first design and afterwards put your information in. This is the situation: I designed the database in such a way that time series of performance rates should be inserted in their original currency (Let's call this that original-currency table). Ive put some queries in which all performance rates can be converted into the same currency. With this "same-currency"- query" I perform calucations that are send to output. As we already have many times series in Excel files, I have tranfered these at once into Access ( in their original currency). However, it is the idea that the database be updated monthly. Too late, i heard that the monthly updates of the performance rates will be delivered all in the same currency. This would imply that should I append them to the "original currency-table" ( which is attached to the related product), Access will think they are in their original currency and convert them again ginving me incorrect information. I thought that maybe I could append them directly to the "same-currency"-query. But im not sure whether u CAN append to a query in the first place. And should this be possible, then I will not have the updates available in the "original-currency-table". Converting all the data I have now in my database manually I do not consider an option.. :-) I hope it is clear and I hope someone cna help me out with this. Greetings, Stacey
View Replies !
Calculate Future Dates Based On Inputs
Hello, Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ? For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually. So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example). Any help would be greatly appreciated. Thank you
View Replies !
Taking Letters From A String
Hi all, I'm not sure if this is possible ... I have a table which has contact details in it... two fields are FirstName and Surname. I want to create a new field called initials which takes the first letter from each of the former two fields. Is it possible to do this using an update query? Or any other way maybe?
View Replies !
|
TRACKER
