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 RepliesI want VbA code to find a date 1 less than todays date taking into account weekends/Bank Holidays.
View 8 Replies View RelatedI 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.
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
Is 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).
Currently 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 RelatedI 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 ?
I 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] ....
Is it possible to create MDB-bank without Access ?
Thank you very much!
Aleksandra
:)
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:
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.
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
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
I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?
code is below.
Code:
Function SyncEmployes()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
[Code].....
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......
I'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??
I want to create a form where i can be recording the deposits and withdrawals of my bank account!
Let's say I don't have any deposit. If I want to add then i would click on the "+Deposit money" button to open a form to add let's say 10$.
so my total would be 10$.
Next record would to add 30$
so my new total would be 40$ (10$ + 30$)
NOW if i make a withdrawal from my account i would like to click on the "- withdraw money" and so my total would change!
so if i withdraw 5$ the new total would be
35$ (total 40$ - 5$)
how can i do that?!!!
Looking for efficient table structure for this. Let's say I have a list of contractors who will get paid a certain amount of money each week. The amount of money changes each week by measuring the amount of work done, giving it a price and calculating it. The fields are these:
[pk]WeeklyWorkID
WorkID
Quantity
UnitPrice
Total=Qty*UnitPrice (not stored in this table)
EmployeeID
WeekID
So far, it's all good, but we wish to pay the contractors in an unusual way. Let's say he makes $16,000 of work this week, and we want to pay him using cash AND/OR either one or multiple debit cards. For example:
Cash: $1,500
Debit card 45: $7,500
Debit card 71: $4,000
Debit card 13: $3,000
Total = $ 16,000
We can only deposit $7,500 max in each card. But it's even worse... two or more contractors can SHARE the card and it's not always the same card. Sounds pretty funky, but it's what the company wants to do. So, what would you recommend me in this case?
I'm guessing I'm gonna need these tables: tbWeeks to hold the week number, tbCards to store the cards' info, and an extra table to make a many to many relationship between the cards and the employees.
How to build query to give daily balance across bank accounts? (to then plot in a graph)
Assumptions:
* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX.
* Table BANKS which has BANK_ID and TITLE
Would like a query that gives: Supply StartDate and EndDate for the query:
Date Bank1Balance Bank2Balance Bank3Balance TotalBalance
1/1/15 $100 $200 $100 $400
1/2/15 $200 $200 $100 $500
.
.
.
etc
I was looking through some of the stuff access has to offer when I came across the calender control. I really like its set up and what I would like to do is create a switchboard to link to the calender then have the user click on the correct date. I need the date clicked to create a table based on the date. I click 2/5/5 the table is labeled 2/5/5. If possable, after the date is clicked I need a form to open for inputing numbers and i want those numbers going into the table based on the clicked date. This possable?
Thanks
Chris :rolleyes:
Hi Ya,
Has Anybody got a list of UK National holidays IE Bank Holidays Etc plus Scotland and Island.
many thanks
mick
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.
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
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 1 Replies View RelatedHi 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
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.