Hoping someone can help with this! I am receiving a #error when subtracting two dates when one or both of the date fields is blank. I created a function called Workdays which removes weekends & holidays. I am just not sure how to get rid of the #error. Below is the code that I am using.
Wrkdays1: Workdays([startDate),[endDate])
When both the startDate and endDate fields contain dates, it works perfectly....instead of the #error, I need the field to be blank because I need to do additional queries off of this data.
Thanks
5 846
Hi Mark,
Create new fileds in query with if statement.
Something like : - EndDt: iif([EndDate] is null,0,[EndDate])
and similarly for StartDate... StartDt - StartDt: iif([StartDate] is null,0,[StartDate])
then subtract these fields.. and check
Would you post your Workdays() Function?
Hi Narender,
Thank you for responding! I followed your suggestion and had some issues but I finally figured it out. My issue was if I had a startdt but no enddt. I would get an answer like 30378. This makes sense because it is subtracting a date from nothing. My fix was I created another field and set anything to 0 that was great than 1,000.
Really appreciated your help on this!
Mark
Hi jforbes,
I am using the workdays function from the link below that removes weekends & holidays. You can copy the information from the link into two modules one "week days" and one "work days". You will also need to create a table for holidays.
Once that information is loaded in your database, you can query off of a table that has a start date and end date by doing the follwing:
Wrkdays1: Workdays([Startdate],[Enddate]) https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
You may find the following helpful:- -
-
Function EasterDate(Yr As Integer) As Date
-
-
Dim Da As Integer
-
Da = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
-
EasterDate = DateSerial(Yr, 3, 1) + Da + (Da > 48) + 6 - ((Yr + Yr \ 4 + _
-
Da + (Da > 48) + 1) Mod 7)
-
-
End Function
-
-
-
Public Function GoodFriday(Yr As Integer) As Date
-
-
GoodFriday = DateAdd("d", -2, EasterDate(Yr))
-
-
End Function
-
-
Public Function EasterMonday(Yr As Integer) As Date
-
-
EasterMonday = DateAdd("d", 1, EasterDate(Yr))
-
-
End Function
-
-
Public Function AscensionDay(Yr As Integer) As Date
-
-
AscensionDay = DateAdd("d", 39, EasterDate(Yr))
-
-
End Function
-
-
Public Function FirstSpringHoliday(Yr As Integer) As Date
-
-
FirstSpringHoliday = DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7)
-
'DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)
-
-
End Function
-
-
Public Function LastSpringHoliday(Yr As Integer) As Date
-
-
Dim TheDay As Integer
-
-
TheDay = Day(DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7)) ' First Spring bank holiday
-
-
TheDay = TheDay + 28
-
If TheDay <= 31 Then
-
LastSpringHoliday = DateAdd("d", 28, DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7))
-
Else
-
LastSpringHoliday = DateAdd("d", 21, DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7))
-
End If
-
-
End Function
-
-
Public Function ChristmasDay(Yr As Integer) As Date
-
-
ChristmasDay = DateSerial(Yr, 12, 25)
-
-
End Function
-
-
Public Function BoxingDay(Yr As Integer) As Date
-
-
BoxingDay = DateSerial(Yr, 12, 26)
-
-
End Function
-
-
Public Function NewYearsDay(Yr As Integer) As Date
-
-
NewYearsDay = DateSerial(Yr, 1, 1)
-
-
End Function
-
-
Public Function Scots2January(Yr As Integer) As Date
-
-
Scots2January = DateSerial(Yr, 1, 2)
-
-
End Function
-
-
Public Function FirstAugustHoliday(Yr As Integer) As Date
-
-
FirstAugustHoliday = DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7)
-
'DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)
-
-
End Function
-
-
Public Function LastAugustHoliday(Yr As Integer) As Date
-
-
Dim TheDay As Integer
-
-
TheDay = Day(DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7)) ' First Spring bank holiday
-
-
TheDay = TheDay + 28
-
If TheDay <= 31 Then
-
LastAugustHoliday = DateAdd("d", 28, DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7))
-
Else
-
LastAugustHoliday = DateAdd("d", 21, DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7))
-
End If
-
-
End Function
-
-
Public Function ThanksgivingDay(Yr As Integer) As Date
-
-
ThanksgivingDay = DateSerial(Yr, 11, 29 - Weekday(DateSerial(Yr, 11, 1), vbFriday))
-
-
End Function
-
Phil
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sondra Wilson |
last post by:
I have created a sorted query. The total(records) I get in the query is
correct. My problem lies with getting that total into my report.
I use count to insert the total number of records in my...
|
by: Rod |
last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some
reason, opening some ASP.NET applications we wrote is producing the
following error message:
"The Web server reported...
|
by: Punker |
last post by:
Hi guys,
I am trying to create export specifications for one of my queries. Now when I run the query on its own, it works perfectly. However when I try to export the data I get back the error...
|
by: s_wadhwa |
last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,...
|
by: ShastriX |
last post by:
Getting a weird error while trying out a query from Access 2003 on a
SQL Server 2005 table.
Want to compute the amount of leave taken by an emp during the year.
Since an emp might be off for...
|
by: chitara |
last post by:
is there any posibility to block mysql port or someting?
I am getting "Error ,query failed" during adding any data through the admin panel of that website:-(
same softwares(php, mysql and IIS)...
|
by: =?Utf-8?B?bXNjZXJ0aWZpZWQ=?= |
last post by:
Has anyone successfully used an Access query from .NET? I am trying to do
this and am getting a weird error. .NET calls queries 'stored procedures'.
The error I am getting says "Schema could not be...
|
by: mrityunjay11 |
last post by:
Error
SQL query:
CREATE TABLE per(
item_name varchar,
item_number int,
payment_status int,
payment_amount int,
payment_currency int
);
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am...
|
by: AccessBeetle |
last post by:
SELECT DISTINCT .Contact1Index, Contacts., Contacts., Contacts., Contacts., Contacts.prefix, Contacts.Suffix, Contacts., Contacts., Contacts.city, Contacts.state, Contacts.zip, ., tblStatusCloseout.,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |