I need to create a database that will be used to schedule patches. Many of our patches happen on the 1st Saturday of the month, but some happen on the 2nd Thursday after the 1st Tuesday every month.
Ive been attempting to use the DATEPART and WEEKDAY functions but without success. Figuring out the 1st Saturday shouldnt be to hard, even if I havent managed to figure that out just yet, but the other is beyond my experience.
4 5178
I need to create a database that will be used to schedule patches. Many of our patches happen on the 1st Saturday of the month, but some happen on the 2nd Thursday after the 1st Tuesday every month.
Ive been attempting to use the DATEPART and WEEKDAY functions but without success. Figuring out the 1st Saturday shouldnt be to hard, even if I havent managed to figure that out just yet, but the other is beyond my experience.
Here's your answer: -
-
Function FirstSatOfMonth() As Date
-
-
For x = 0 To 6
-
If Weekday(DateSerial(Year(Date), Month(Date), 1) + x, vbUseSystemDayOfWeek) = vbSaturday Then
-
FirstSatOfMonth = DateSerial(Year(Date), Month(Date), 1) + x
-
End If
-
Next x
-
End Function
-
-
Function SecondThursdayafterFirstTuesday() As Date
-
For x = 0 To 6
-
If Weekday(DateSerial(Year(Date), Month(Date), 1) + x, vbUseSystemDayOfWeek) = vbTuesday Then
-
SecondThursdayafterFirstTuesday = DateSerial(Year(Date), Month(Date), 1) + x + 9
-
End If
-
Next x
-
End Function
-
-
Wow! Thanks for the quick response. Its midnight here, didnt think anyone would reply until tomorrow morning. You rock!
I used the information you gave me to create 33 functions. They all work perfect now, but Ive run into another problem.
The following is a quick example of what I am trying to do:
---
Private Sub MonthCombo_Afte rUpdate(Cancel As Integer)
[test] = First_Saturday( )
End Sub
---
Basically when the user changes the month in the form header it will update all the patch windows using their particular function.
First_Saturday( ) is in a module named DateCalculation .
Originally I had attached each function directly to related textbox as default value which worked just fine until I figured it need the flexibility of changing the month value.
I had changed a function to replace Month(Date) with [Form]![test]![MonthCombo] which works, but only as the form loads. I need the data to update if the month is changed.
This is the following error I get as the AfterUpdate runs...
The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.
I used the information you gave me to create 33 functions. They all work perfect now, but Ive run into another problem.
The following is a quick example of what I am trying to do:
---
Private Sub MonthCombo_Afte rUpdate(Cancel As Integer)
[test] = First_Saturday( )
End Sub
---
Basically when the user changes the month in the form header it will update all the patch windows using their particular function.
First_Saturday( ) is in a module named DateCalculation .
Originally I had attached each function directly to related textbox as default value which worked just fine until I figured it need the flexibility of changing the month value.
I had changed a function to replace Month(Date) with [Form]![test]![MonthCombo] which works, but only as the form loads. I need the data to update if the month is changed.
This is the following error I get as the AfterUpdate runs...
The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.
The sub routine that you sent, I'm assuming the [Test] is a control on the current form this sub is in?
If so, simply do the following: -
-
Private Sub MonthCombo_BeforeUpdate(Cancel As Integer)
-
-
me.Test = First_Saturday()
-
-
End Sub
-
Hope that helps,
Joe P.
p.s. The AfterUpdate Event doesn't support (Cancel as Integer). That's why you were getting the error.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Paolo |
last post by:
I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).
I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.
The match should therefore be done between the date stored in the DOB
field and the current date.
|
by: Mark Hall |
last post by:
Our reporting cycle goes from Wednesday to Wednesday an I need to
calculate the next Wednesday after the update was received.
If an upate comes in on say a Tuesday (eg 20 Jan 04) then I want a
field on the form to show Wednesdays date (eg 21 Jan 04).
However, if the update comes in on Thursday (eg 22 Jan 04) then the
field would be updated with the date 28 Jan 04.
Any help much appreciated.
|
by: Paul Mendez |
last post by:
Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?
Can someone help? What I am trying to do is create a query that will
end up looking like the bottom example. The above query is a
calculated totals query with an...
|
by: Gustavo G. Rondina |
last post by:
It is possible to caclulate every year's easter using simple
mathematical operations. Here is a code that does the trick:
http://www.brlivre.org/c/easter.c
I found the math scheme in an american scientific issue, march
2001. The article about the calendars is very interesting.
Have fun...
--
|
by: Jason |
last post by:
I've created a service in c# that performs a certain action at a particular
day of the month
I want this service to kick off the action every third Tuesday of the month.
How would I set the service to run a desired action if the day is equal to
the third day of the month? Would that be done by programming it or using a
timer ? How would that be done.
| |
by: =?Utf-8?B?UGF1bA==?= |
last post by:
HI I have a stored procedure that returns data with a date field in the form
of a DateTime type. I need to place data in variables based on days of the
week starting with the first thursday of the month. So the week would be
week 1= (first thursday of the month through the next wed). So for example
for July 07 the first thursday is july5th so the first week would be thursday
july 5th , friday july 6th, sat july 7th, sun july 8th, mon...
|
by: cbalian |
last post by:
I am looking for a TSQL code that would calculate a specific date each month that varies based on the following condition:
I need the result that would return the date of the Thursday after the Last Wednesday of each month.
I'm guessing a code that would be (Last Wednesday of Month) + 1 Day.
My problem is I can find ways to calculate a day within a week, the last day or first day of a month, add/subtract as needed from that. But...
|
by: ncsthbell |
last post by:
I am having problems getting the end date to calculate correctly.
I start with Quarter '03/02', (YY/QTR), for this it means it is for the 2nd qtr of 2003. My goal is to get the begin & end dates for each 'month' in the quarter, hence, I want to calculate the begin & end of the month dates for April, May & June 2003 :
so my starting point is:
firstMonthBegin = vcBegMM & "/" & vcBegDD & "/" & vcBegYY
firstMonthEnd = vcEndMM &...
|
by: =?Utf-8?B?QWw=?= |
last post by:
I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |