Modules & VBA :: Find Previous Date Taking Into Account Weekends / Bank Holidays
Mar 25, 2015I want VbA code to find a date 1 less than todays date taking into account weekends/Bank Holidays.
View Replies
ADVERTISEMENT
Modules & VBA :: Calculating A Date While Taking Into Account Holidays
Mar 17, 2015I have been looking for quite some time for some vba code that would return a date based on values in 2 fields.
In other words, I already have a field that returns transit days based on the customer chosen. End users will then enter a DueDate for the order. I want a field that returns a "ScheduledShipDate" based on [DueDate]-[TransitDays]. The part that makes this more difficult, in my opinion, is the fact that I also need to take into account some specific holidays.
I have already constructed a table with a list of the 6 company holidays and their corresponding dates. [tblHolidays.HolidayDate]
Unfortunately, my vba knowhow might as well be limited to copy and pasting as I do not need to use it all that often.
Future Date - Taking W/e & Holidays Into Account
Feb 22, 2007Hey 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.
Access Calender, Ignore Weekends And Bank Holidays
Apr 2, 2008I'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 1 Replies View RelatedQueries :: Date Difference Excluding Weekends And Holidays
May 6, 2014Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?
For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).
Get Date (minus One Day) Excluding Weekends And Holidays In SQL Query To Use In Access
Oct 11, 2012I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
Modules & VBA :: Calculate DueDate Excluding Weekends And Holidays
Jul 31, 2014Currently I use a module to calculate DueDate, excluding weekends and holidays. I store all the holiday in a tblHoliday and reference this table in the module. A formula then calculated the DueDate. This works great in an access form, but I now realize that I need that value stored so it appears on the table as well, but do not know how to accomplish this.
View 9 Replies View RelatedModules & VBA :: Calculate A Field That Excludes Weekends And Holidays - Code Error
Sep 24, 2014I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
Bank Holidays: Advice Needed
Mar 31, 2008hi
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
Reports :: Time Difference Excluding Weekends And Holidays In Hours From Now
Sep 6, 2013I would like to add time elapsed since an entry was made excluding weekends public holidays and calculating 8 hours a day (from 6am to 2 pm) since the entry was made till the generation of the report.
How shall I approach this in terms of programming ?
Modules & VBA :: Using Previous Records To Find Totals
Feb 18, 2014Basically what I have is a form where operators can input certain information about a production process, in particular the kg input which has gone into the 'basket'. However this is divided by customer order number and sometimes more than one customers order is placed into the same basket. This means that the weight of the record is not representative of the weight in the basket, and it is the weight of the basket which determines efficiency in the process.
Each basket is designated a 'Disc Number' which is put in the basket so the parts contained can be identified after the process. What I am trying to do is right a code, whether in my table or form (or somehow creating a query) which compares the disc number of the previous record (or previous 2 or 3) and if they are the same the totals are combined. Each record the user places has a hidden ID which keeps them in order, and the records with the same disc number will always be entered after one another in that sequence.
How would I be able to code something to deal with this and where would I put it?The table in question which contains the records is called Thetatbl and then form it links to is Thetafrm. The weight of each record is contained in Orderkgtxt.
Modules & VBA :: Compare Admission Date To Previous Discharge Date
Jan 13, 2015I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
Queries :: Bank Holiday Date - Multiple IIF Statements
Jun 1, 2015I've a nice formula which work the majority of the time, the only time this doesn't work is Bank Holidays. Is there a way to build on this:
>=Date()-IIf(Weekday(Date(),2)<2,4,2) And <=Date()-1
The above simply runs a report for the last working day, expect if today is a Monday then it runs Fri-Sun.
Im assuming if I know the dates of the Bank Holidays I could hardcode them into the beginning with multiple IIF statement followed by the one above??
Date Conversion To Weekday And Weekends
Mar 12, 2008Hi all.
I can complete this in excel no problem with monday through sunday being 1 -7, but is the same possible in access.
i.e. 12/03/08 = Wednesday.
Many Thanks Dean:confused:
Update:
DoW: Weekday([Calldate],0)
Yesterday Date Ignoring Weekends
Dec 9, 2013I'm using the Date()-1 to populate a form with yesterday's date for easier data entry. However, on Mondays I always have to change the date to Friday's date because it is populating yesterday's date (which in this case is Sunday). Is there an access expression that calculate yesterday's date but ignores weekends? So that on Monday yesterday would mean Friday?
View 4 Replies View RelatedModules & VBA :: Find Date Which Is 6 Months Back From Today Date
Jan 27, 2015I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
View 1 Replies View RelatedCalculate Date Difference Excluding Weekends
Apr 25, 2014I am trying to Calculate the Date difference between "Material Submit Data" and "Current Date" excluding Weekends.
View 2 Replies View RelatedCalculating Date Difference Excluding Weekends In Access
Aug 8, 2006I 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
Modules & VBA :: Calculate Deadline With Workdays And Holidays
Mar 19, 2014I am trying to get a module set-up that will add a number of workdays (no weekends, no holidays) to a date that is mentioned in a form and put the resulting deadline date in another field on the same form (date or remaining days to deadline would be even better).
Now it gets a little tricky, the module will need to select different amounts of workdays to be added to the date in the form depending on what status is selected (different statusses have different amounts of days).
I'll probably need:
List of holidays
List of days required per status
Start date (to which the days can be added).
Example:
Status = "DQ" which has 2 days to work with.
Start date in form = "21/03/2014".
Result = 25/03/2014 or preferably "2 days remaining".
Modules & VBA :: Find Last Day Of Month From A Date Field
Dec 10, 2013I'd like to further automate our invoicing system and need a field which has the last day of the month an item was completed.
Currently we have a field in the table called [Date Done]. I'm planning on adding a further field [Tax_Point].
I'd like the field to select the [date done] value and enter the final day of that month, unless, the final date of that month is in the future. in which case it would need the current date.
We create invoices at sporadic times of the month, and in the next month for the previous month; hence the need for a system date check.
Modules & VBA :: Find Each Date In A Memo Field
Jul 21, 2014Memo field is called [Notes] and data is like this...
5/05/14 - Perry was on another call. LM 2/05/14 - Perry only at centre in the mornings, need to speak to him before sending samples. 13/06/13 - Perry in a meeting. lm 30/05/13 - See Little Hampton Early Learning - s/w Perry, has already received sample and info 29/05/13 - s/w Aspi, said to cb tomorrow and speak to Perry
I want to find each date in the Notes field so I can split the memo field data into another table where the memo field will become multiple records that hold date, text and customer/prospect ID fields. The customer table was easy because there was a <Div> tag before each date. However in the Prospects table there are no tags so how to change my vba code to search for each date before I split off the data.
Here is the part of the VBA code I used to find the <Div> tag in the customer notes field. How to find each date in the memo field? The date is in d/mm/yy format?
If Not rst.EOF Then
Do
StrSplit = Split(rst![Notes], "<Div>")
For x = 1 To UBound(StrSplit)
Subquery To Find Previous Value
Jun 20, 2013I have created this sub query:
Code:
(SELECT SUM(Cash)
FROM qryAccountSetup AS T2
WHERE T2.ClientID = [qryAccountSetup].ClientID
AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,
How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause....
Code:
(SELECT (Cash)
FROM qryAccountSetup AS T2
WHERE
(Select (SELECT MAX(T3.MonthlyDate)
FROM qryAccountSetup AS T3
WHERE T3.ClientID = qryAccountSetup.ClientID
AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash,
But i keep getting error.
What i am trying to do is get the previous cash value.... For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.
ClientID..MonthlyDAte
1...........03/01/2013
1...........31/01/2013
1...........01/02/2013
1...........and so on.
There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.
Taking One Date From Another To Give Number Of Days Left
Dec 11, 2006HI, 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"
Queries :: Taking A Date From List Box And Entering It Into Query In VB
Sep 4, 2013I have a process that lifts a highlighted date from a List Box and puts it in the Criteria of a query. The process manages to move the numbers from one place to another, but ends up giving me a data mismatch in the query. The process is as follows
Private Sub Command8_Click()
'Set it all up for Panel Meeting selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
[Code] ....
The Immediate debug shows...
SELECT * FROM tbl_Panel_Meeting_Dates WHERE tbl_Panel_Meeting_Dates.PanelDates IN ( '1/07/2013' );
I'm pretty sure (althiough always stand to be corrected!) that I need it to come out as
In (#1/07/2013#)
How can this be achieved?
Modules & VBA :: Function To Find ACCDB File Date Created?
Jun 5, 2014Is there a function I could use to find the date listed in the "date created" property of an Access file?
View 4 Replies View RelatedModules & VBA :: Cannot Set Outlook Send Using Account
Jul 13, 2013A client wants to e-mail newsletters using a non-default Outlook account. The code below does everything the client needs except setting the SendUsingAccount. In debug I can see that the correct account is assigned, yet all of my testing results in e-mails where the From line is the default account. The test setup uses my own isolated SMTP server, so when I look at the e-mails sent the sender is the default account. The default account's Sent folder shows the sent mail, which is not what we want.
Code:
Sub prepEmail()
Dim frm As Form, startDate As Date, endDate As Date
Dim rs As DAO.Recordset, strSQL As String, intNewsLetter As Integer
Dim rsEmail As DAO.Recordset, rsNewsletters As DAO.Recordset
Dim OlApp As Object, ol As Object
Dim olMail As Object, olAcct, olAcctTemp
[code]...