Windows XP - Access 2003
I've been researching this for about 3 weeks now and I'm no closer then when I started so any help you all give me will save my hair. I'm VERY new and learning as I go so bare with me.
What I want to do is generate a new record for each date an event is held on. The events are reoccuring events. Daily, weekly, etc.
What I've done so far:
I have a foum that the user enters the Start date and End Date.
They have an option to pick Daily, Weekly, Bi-Weekly, Day in Month, or Week in Month from a Value Listbox.
Then they select (via a checkbox) the weekday they want the event to fall on.
Daily - Generates the dates from start to end date. Each date is it's own record. (works)
Weekly & Bi-Weekly - Generates the dates from start to end date based on which weekday box is checked. Each date is it's own record. (works)
- User can select one or more weekdays (IE Monday or M, W & F) via the boxes and it will generated a record for each of those days between the start and end date.
Date in Month - If the start date is Nov 12, 2006 and the end is Jan 12, 2007. It will generate the records for Nov 12, Dec 12, & Jan 12. (Works)
My problem:
Week in Month - If The conferance is every 2nd Tuesday of each month.
- User selects Tuesday checkbox
- inputs start date & end date. (Nov 7 & Jan 9th)
- The three records should be Nov, Dec 5, Jan 9.
The code itself should be able to pull which week Nov 7 is on and spit out Dec 5 by itself.
Solution: (Not sure how to code it)
MyWeek = The week # from the start date - 1
NewDate = Dateadd("Find the first weekday (tuesday) of next month", MyWeek, StartDate)
Thanks for any help you all can give.
9 3650 NeoPa 32,557
Recognized Expert Moderator MVP
Do you have any example code to modify?
This will save time coding what's already done and also give hints as to any gaps in your explanation.
What format do you need the output in for instance?
To check for the weekday: - Function SkipThisDay(intDow As Integer) As Boolean
-
SkipThisDay = False
-
Select Case intDow
-
Case 1 'Sunday
-
If Me![Sunday] <> True Then SkipThisDay = True
-
Case 2 'Monday
-
If Me![Monday] <> True Then SkipThisDay = True
-
Etc for each weekday.
-
End Select
-
End Function
Code to create the new record. - Dim dblCurrentTime As Date
-
Dim dblBeginTime As Date
-
Dim dblEndTime As Date
-
-
While dblCurrentDate <= dblEndDate
-
If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
-
rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
-
If rstSchedule.NoMatch Then
-
rstSchedule.AddNew
-
rstSchedule![TicketID] = lngTicketID 'pulls from the form
-
rstSchedule![Date] = dblCurrentDate
-
rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![Ports] = lngPorts 'pulls from the form
-
rstSchedule.Update
-
fPreviousRecs = False
-
Else
-
lngScheduleID = rstSchedule![ScheduleID]
-
End If
-
End If
-
Select Case Me![Type]
-
Case "Week In Month" 'Monthly Week In Month
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate) ' which isn't right but it's temp till I figure this out. At least it gives the right month.
-
Case "Date In Month" 'Monthly Date In Month
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
-
Case "Bi-Weekly" 'Bi-Weekly
-
dblCurrentDate = dblCurrentDate + 14
-
Case "Weekly" 'Weekly
-
dblCurrentDate = dblCurrentDate + 1
-
Case "Daily" 'Daily
-
dblCurrentDate = dblCurrentDate + 1
-
End Select
-
Wend
Do you need more? As I said I'm relitivly new and learning as I'm going so I'm not compleatly sure what is relivent.
Do you have any example code to modify?
This will save time coding what's already done and also give hints as to any gaps in your explanation.
What format do you need the output in for instance?
NeoPa 32,557
Recognized Expert Moderator MVP
Try this : - Dim dblCurrentTime As Date
-
Dim dblBeginTime As Date
-
Dim dblEndTime As Date
-
Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
-
-
If [Type] = "Week In Month" Then
-
intWeekday = Weekday(dblCurrentDate)
-
intWeekNo = (Day(dblCurrentDate) - 1) Mod 7
-
End If
-
While dblCurrentDate <= dblEndDate
-
If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
-
rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
-
If rstSchedule.NoMatch Then
-
rstSchedule.AddNew
-
rstSchedule![TicketID] = lngTicketID 'pulls from the form
-
rstSchedule![Date] = dblCurrentDate
-
rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![Ports] = lngPorts 'pulls from the form
-
rstSchedule.Update
-
fPreviousRecs = False
-
Else
-
lngScheduleID = rstSchedule![ScheduleID]
-
End If
-
End If
-
Select Case Me![Type]
-
Case "Week In Month" 'Monthly Week In Month
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
-
intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
-
If intDayNo > intWeekday Then intDayNo = intDayNo - 7
-
intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
-
dblCurrentDate = CDate(intDayNo & _
-
Format(dblCurrentDate, " mmmm yyyy"))
-
Case "Date In Month" 'Monthly Date In Month
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
-
Case "Bi-Weekly" 'Bi-Weekly
-
dblCurrentDate = dblCurrentDate + 14
-
Case "Weekly" 'Weekly
-
dblCurrentDate = dblCurrentDate + 1
-
Case "Daily" 'Daily
-
dblCurrentDate = dblCurrentDate + 1
-
End Select
-
Wend
NB. Date fields are better named dat????? rather than dbl which gives a false impression.
I think this should work for you, but I can't test it for real.
Some nice code in here btw.
I'm sorry my additions are not documented - please ask if you have any specific questions.
To be honest a majorty of the code is snipped from one place or another and put in and fiddled with till it works. To be honest I've never thought to change "dbl" to "dat" in fear I would have to start from square one.
The modification you gave me sorta worked
Inserts the first date then finds the second week in the month and creates the new date. So while step in the right direction (thank you so much btw) not quite there.
What does the the bold part in this?
intWeekNo = (Day(dblCurrentDate) - 1) Mod 7
the "Mod" was the only thing I didn't get and I can't seem to pull it up refrance of it on the web to research.
NeoPa 32,557
Recognized Expert Moderator MVP
What does the bold part in this mean?
intWeekNo = (Day(dblCurrentDate) - 1) Mod 7
the "Mod" was the only thing I didn't get and I can't seem to pull it up or reference of it on the web to research.
Modulo arithmetic.
Unfortunately it should have read : - intWeekNo = (Day(dblCurrentDate) - 1) \ 7
Mod Operator
Used to divide two numbers and return only the remainder.
Syntax
result = number1 Mod number2
The Mod operator syntax has these parts:
Part Description
result Required; any numeric variable.
number1 Required; any numeric expression.
number2 Required; any numeric expression.
Remarks
The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. For example, in the following expression, A (result) equals 5.
A = 19 Mod 6.7
Usually, the data type of result is a Byte, Byte variant, Integer, Integer variant, Long, or Variant containing a Long, regardless of whether or not result is a whole number. Any fractional portion is truncated. However, if any expression is Null, result is Null. Any expression that is Empty is treated as 0.
\ Operator Example
This example uses the \ operator to perform integer division.
Dim MyValue
MyValue = 11 \ 4 ' Returns 2.
MyValue = 9 \ 3 ' Returns 3.
MyValue = 100 \ 3 ' Returns 33.
Strange that it works correctly with \ but not with mod.
The only error I have now is if something starts on the 5th week in a month. It should return basicly loop itself back to the last week.
4th Mon: Jan 22, Feb 26, Mar 26, Apr 23
5th Mon: Jan 29, Feb 26, Mar 26, Apr 23
NeoPa 32,557
Recognized Expert Moderator MVP
Strange that it works correctly with \ but not with mod.
No it's not - that's exactly what you'd expect.
Mod was my mistake - it always should have been \.
The only error I have now is if something starts on the 5th week in a month. It should return basically loop itself back to the last week.
4th Mon: Jan 22, Feb 26, Mar 26, Apr 23
5th Mon: Jan 29, Feb 26, Mar 26, Apr 23
Now you're being greedy :) - Dim dblCurrentTime As Date
-
Dim dblBeginTime As Date, dblEndTime As Date, datTmp As Date
-
Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
-
-
If [Type] = "Week In Month" Then
-
intWeekday = Weekday(dblCurrentDate)
-
intWeekNo = (Day(dblCurrentDate) - 1) \ 7
-
End If
-
While dblCurrentDate <= dblEndDate
-
If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
-
rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
-
If rstSchedule.NoMatch Then
-
rstSchedule.AddNew
-
rstSchedule![TicketID] = lngTicketID 'pulls from the form
-
rstSchedule![Date] = dblCurrentDate
-
rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM") 'pulls from the form
-
rstSchedule![Ports] = lngPorts 'pulls from the form
-
rstSchedule.Update
-
fPreviousRecs = False
-
Else
-
lngScheduleID = rstSchedule![ScheduleID]
-
End If
-
End If
-
Select Case Me![Type]
-
Case "Week In Month" 'Monthly Week In Month
-
datTmp = dblCurrentDate
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
-
intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
-
If intDayNo > intWeekday Then intDayNo = intDayNo - 7
-
intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
-
'Handle invalid dates
-
On Error Resume Next
-
datTmp = CDate(intDayNo & Format(dblCurrentDate, " mmmm yyyy"))
-
On Error GoTo 0
-
'If setting datTmp failed then it still has last month's date in it
-
If Month(datTmp) = Month(dblCurrentDate) Then
-
dblCurrentDate = datTmp
-
Else
-
dblCurrentDate = CDate(intDayNo - 7 & _
-
Format(dblCurrentDate, " mmmm yyyy"))
-
End If
-
Case "Date In Month" 'Monthly Date In Month
-
dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
-
Case "Bi-Weekly" 'Bi-Weekly
-
dblCurrentDate = dblCurrentDate + 14
-
Case "Weekly" 'Weekly
-
dblCurrentDate = dblCurrentDate + 1
-
Case "Daily" 'Daily
-
dblCurrentDate = dblCurrentDate + 1
-
End Select
-
Wend
No it's not - that's exactly what you'd expect.
Mod was my mistake - it always should have been \.
Now you're being greedy :)
Naw, never greedy just learn by looking at code.. Got about half of what you posted before I was stumped and came back to see if there was anything else. Worked like a charm! Thank you so much for your help NeoPa.
NeoPa 32,557
Recognized Expert Moderator MVP
No problem AW.
If there's anything you don't understand you only have to ask.
The only danger is that I may waffle on a bit rather than not explain enough.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: cg_news |
last post by:
In short, what I am trying to do is, based on a date, calculate the week of
year (as described in ISO 8601), then calculate the first and last date in
this week period and return them in the format...
|
by: androtech |
last post by:
Hello,
I'm looking for a function that returns a date range for a specified week
number of the year.
I'm not able to find functions like this anywhere. Any pointers/help would
be much...
|
by: David Morgan |
last post by:
Hi
Hopefully the subject says it all. Given a weekday, (1 to 7) and an
occurrence with in a month, (1 to 4), I need to ascertain the date on which
it first occurs in any given month and year.
...
|
by: Treetop |
last post by:
I have a script for my church that we use for the weekly events. I
currently have it as week of Feb 1, 2003 at the top, then list Sun -
Sat below the date. I have been asked to put the date next...
|
by: Papegoja |
last post by:
Hi,
A week ago I had a question how to get MS Query to return a standard
date to a "YEAR-MONTH" (YYYY-MM) format.
I received a great answer from Jerry Boone!!!
It follows:
SELECT...
| |
by: shsandeep |
last post by:
I used the following query to retrieve the date in dd-mon-yyyy format.
db2 => SELECT RTRIM(CHAR(DAY(COVG_TYP_STRT_DT))) || '-' ||
RTRIM(MONTHNAME(COVG_TYP_STRT_DT)) || '-' ||...
|
by: bruce24444 |
last post by:
I have a database which assigns warranty claims to people with a main screen showing number of files assigned to each person. The number assigned shows day, week, month and year numbers so they can...
|
by: osward |
last post by:
Hi everyone,
Background
1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >=...
|
by: MNNovice |
last post by:
My form has two text boxes, txtDateFrom and txtDateTo. A macro button with the caption Month is to automatically fill in txtDateFrom and txtDateTo with current month date. My code reads as:
...
|
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: 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: 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,...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
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.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |