Access Calender, Ignore Weekends And Bank Holidays
I'm using access 2007 and have some date fields in my forms, i'd like to be able to blank out weekends and public holidays from the calender/date selector in these fields so theres no way those dates can be selected. Is this possible? Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Calculating Date Difference Excluding Weekends In Access
I have a query that is looking to calculate staff absence. Absence Start date & Return to work date Looking to calculate number of days (not a problem) but to exclude weekends ie saturday and sunday ???? Not sure if this can be done --- any assistance would be greatly received. thanks and regards ian watson Yarm Cleveland UK
View Replies !
Access 2K3 Append/Update Ignore Non Null Cells!
Hi, I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for each record. I began collating the information in Excel, but found myself getting lost and even experienced major data loss on at least 2 occasions. I have now decided to ditch Excel and use Access 2003 instead. I have imported my master spreadsheet, however as I have various contacts sending in their respective information in Excel spreadsheets with same types of fields, and also need to import data that has already been sent in. I’m thinking that it would be better for me to create update and append queries, especially as there is going to be a stage 2, where I will be requesting additional (field) information. There is a big chance that some of the contacts will send in info for the same item (record), which means that there is a risk of duplication, which I have removed as I have a unique identifier which will be the primary key! Is that right or should it be indexed? The main problem that I have is that I want Access to ignore the target cell if it has a value in it! Meaning that I would not like Access 2003 to overwrite the cell with valid data in it, with a blank cell! As I need the database to grow! Can anyone suggest a way that I can do this please. TIA Bazdaa
View Replies !
Adding Calender Option To Access
my departments seems to want to add some sort of notification feature to our database. It's supposed to notify the department when a certain date is approaching. How do you even begin to think about adding this feature into Access? I don't know where to start. I'm no Access guru. :confused:
View Replies !
Access Calender Size Problem
Im Making a Room Booking System for Our Meeting room, there is only one room, so its easy to use the Calender(MSCAL.Calendar.7)value to pull a query for whats happening on anyone day, this also allows to add new entries, my problem is Ive writting it in MSA2003, and my main user is in 2000, when she opens the form with the calender on it the fonts are all reset to 1.5 in size, and this is not useable, has anyone seen this before or had something similar, I thought Id just use VBA to reset the font sizes in the Form_open routine, but I cannot get VB to Access those properties of the calender, so Im a little Stumped.
View Replies !
Odd File Format For Import By Bank
Hi: Working with a bank that wants a file from us so they can import into their check reconciliation program. Got the export from the accounting program working for the info they need, but the bank guy says he needs a file structure like this: (first row is header..don't worry about what the stuff means for now) 1234578990000000000000000 (rest of rows are details) 1234589000807091234 1234589000807093456 notice the line breaks between header and rows? Wouldn't this call for a line break and mess up an import program? Bank guy isn't a database person and is clueless Thanks......
View Replies !
Query To Exclude Weekends....
Hello I am new to this site as well as MS Access and SQL. So any help would be appreciated. Sorry if this is too drawn out. I have this query that returns the previous business day's(Monday through Friday) number of docs sent from my department to another department. I would manually go into the SQL view in Access and change the date to yesterday. The easy part was having the code do the previous day. The problem comes Monday morning when the code does the previous day. It will do Sunday which will return nothing since I really want Friday's numbers. I am trying to figure out how to take into consideration if the query is run on Monday morning and to automatically choose Friday. I checked out DayOfWeek() and Case When condition statements but really unsure about syntax and whatnot. Here is the SQL view of the query... SELECT Count(WS_FORM_TEMP.FORM_TEMP_ID) AS CountOfFORM_TEMP_ID, WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS FROM WS_FORM_TEMP GROUP BY WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS HAVING (((WS_FORM_TEMP.CREATED_DATE)=to_char(sysdate-1,'DD-Mon-YYYY')) AND ((WS_FORM_TEMP.RECEIVED_TYPE='1') or (WS_FORM_TEMP.RECEIVED_TYPE='2'))); Thanks in advance.
View Replies !
Due Dates Skipping Weekends
I have a combo box that when selected autofills a due date box. I forgot that I needed to skip weekends and only count business day. For example if you choose standard it adds 2 days to the date but I need it to skip weekends. Any help would be greatly appreciated. Private Sub YourComboName_AfterUpdate() Dim intDays As Integer Select Case Me.YourComboName Case "Standard" intDays = 2 Case "Sensitive" intDays = 5 Case Else intDays = 10 End Select Me.YourDateTxtbox = DateAdd("d", intDays, Date) End Sub
View Replies !
Setting Dates Excluding Weekends.
Hello all, I am a bit stuck in my query. I need to setup a Next Follow-up date based on the last follow-up date excluding weekends. For example: Last Follow-up: 1/10/08. Next follow-up should be 5 days after that. So it should be: 01/17/08. I have a calculated field that calculates the number of days (Counting off weekends) but I can't seem to have the date setup correctly. Thanks
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 !
I've Searched, But Just Not Finding What I Need. Exclude Weekends In Query
Hi, I've been searching the forums, and I find a bunch of posts about the subject, but I just can't seem to really find what i need. What I'm looking for is how to construct a query, that will exclude any entries in my table that might fall on a Saturday or Sunday. What is the easiest way to do this? I'm going to be using the results of this query in a Report and DAP. Thanks Chris.
View Replies !
Calculate Work Hours Excluding Weekends.
Let me just start out by saying that when it comes to Access, I ride on the "little bus", so I apologize in adavance if this is a lame question. I have a table with two date/time fields. One for reporting the date and time of an issue start, the other for the date and time of an issue ending. I'm trying to find a way to get the number of hours between the two without including weekends. I found a module on mvps.org that excludes workdays. However it doesn't seem to work in this case. For instance, if I put in an issue start as 02/10/2006 7:30 am, and the issue end as 02/13/2006 6:45 am, it only counts it as one workday. It won't count the 13th as a workday until the time is >= 7:30 am.:confused: I'm simply stumped. Any help would be greatly appreciated. Thank you,:)
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 !
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 Replies !
Ignore Duplicates
I have 2 tbl's one is class and the other is professor... in my logic i create a new class for each student however, I am trying to make label for the professor and since a professor can teach more than one class i need only one label. How do i do this
View Replies !
How To Ignore Parameter?
Hi there I've been looking for this one for a while. In my query I'm using a date field to extract dates using the between And function. I'm refrencing to txt boxes within a dialog form within the query. For example: Between [Forms]![frmViewByCatagory]![TxtStartDate] And [Forms]![frmViewByCatagory]![txtEndDate] The field actually only lists years rather whole dates . I want to find a way to add an expression so that I can leave the txt boxes blank and access will ingnore the txt boxes and give me all the records. Does anyone have an idea what I can do here. I thought the IIF function along with Null might work but I don't know the syntax. Thanks
View Replies !
|
TRACKER
