By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Date - Day of Week in Month

P: 6
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.
Nov 28 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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?
Nov 28 '06 #2

P: 6
To check for the weekday:

Expand|Select|Wrap|Line Numbers
  1. Function SkipThisDay(intDow As Integer) As Boolean
  2.     SkipThisDay = False
  3.     Select Case intDow
  4.         Case 1 'Sunday
  5.             If Me![Sunday] <> True Then SkipThisDay = True
  6.         Case 2  'Monday
  7.             If Me![Monday] <> True Then SkipThisDay = True
  8.         Etc for each weekday.
  9.     End Select
  10. End Function
Code to create the new record.
Expand|Select|Wrap|Line Numbers
  1.     Dim dblCurrentTime As Date
  2.     Dim dblBeginTime As Date
  3.     Dim dblEndTime As Date
  4.  
  5.     While dblCurrentDate <= dblEndDate
  6.         If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
  7.             rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
  8.             If rstSchedule.NoMatch Then
  9.                 rstSchedule.AddNew
  10.                 rstSchedule![TicketID] = lngTicketID          'pulls from the form
  11.                 rstSchedule![Date] = dblCurrentDate
  12.                 rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
  13.                 rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
  14.                 rstSchedule![Ports] = lngPorts           'pulls from the form
  15.                 rstSchedule.Update
  16.                 fPreviousRecs = False
  17.             Else
  18.                 lngScheduleID = rstSchedule![ScheduleID]
  19.             End If
  20.         End If
  21.         Select Case Me![Type]
  22.             Case "Week In Month"            'Monthly Week In Month
  23.                 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.
  24.             Case "Date In Month"            'Monthly Date In Month
  25.                 dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
  26.             Case "Bi-Weekly"                'Bi-Weekly
  27.                 dblCurrentDate = dblCurrentDate + 14
  28.             Case "Weekly"                   'Weekly
  29.                 dblCurrentDate = dblCurrentDate + 1
  30.             Case "Daily"                    'Daily
  31.                 dblCurrentDate = dblCurrentDate + 1
  32.         End Select
  33.     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?
Nov 29 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Try this :
Expand|Select|Wrap|Line Numbers
  1.     Dim dblCurrentTime As Date
  2.     Dim dblBeginTime As Date
  3.     Dim dblEndTime As Date
  4.     Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
  5.  
  6.     If [Type] = "Week In Month" Then
  7.         intWeekday = Weekday(dblCurrentDate)
  8.         intWeekNo = (Day(dblCurrentDate) - 1) Mod 7
  9.     End If
  10.     While dblCurrentDate <= dblEndDate
  11.         If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
  12.             rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
  13.             If rstSchedule.NoMatch Then
  14.                 rstSchedule.AddNew
  15.                 rstSchedule![TicketID] = lngTicketID          'pulls from the form
  16.                 rstSchedule![Date] = dblCurrentDate
  17.                 rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
  18.                 rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
  19.                 rstSchedule![Ports] = lngPorts           'pulls from the form
  20.                 rstSchedule.Update
  21.                 fPreviousRecs = False
  22.             Else
  23.                 lngScheduleID = rstSchedule![ScheduleID]
  24.             End If
  25.         End If
  26.         Select Case Me![Type]
  27.             Case "Week In Month"            'Monthly Week In Month
  28.                 dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
  29.                 intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
  30.                 If intDayNo > intWeekday Then intDayNo = intDayNo - 7
  31.                 intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
  32.                 dblCurrentDate = CDate(intDayNo & _
  33.                                        Format(dblCurrentDate, " mmmm yyyy"))
  34.             Case "Date In Month"            'Monthly Date In Month
  35.                 dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
  36.             Case "Bi-Weekly"                'Bi-Weekly
  37.                 dblCurrentDate = dblCurrentDate + 14
  38.             Case "Weekly"                   'Weekly
  39.                 dblCurrentDate = dblCurrentDate + 1
  40.             Case "Daily"                    'Daily
  41.                 dblCurrentDate = dblCurrentDate + 1
  42.         End Select
  43.     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.
Nov 30 '06 #4

P: 6
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.
Nov 30 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 30 '06 #6

P: 6
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
Nov 30 '06 #7

NeoPa
Expert Mod 15k+
P: 31,660
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 :)
Expand|Select|Wrap|Line Numbers
  1.     Dim dblCurrentTime As Date
  2.     Dim dblBeginTime As Date, dblEndTime As Date, datTmp As Date
  3.     Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
  4.  
  5.     If [Type] = "Week In Month" Then
  6.         intWeekday = Weekday(dblCurrentDate)
  7.         intWeekNo = (Day(dblCurrentDate) - 1) \ 7
  8.     End If
  9.     While dblCurrentDate <= dblEndDate
  10.         If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
  11.             rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
  12.             If rstSchedule.NoMatch Then
  13.                 rstSchedule.AddNew
  14.                 rstSchedule![TicketID] = lngTicketID          'pulls from the form
  15.                 rstSchedule![Date] = dblCurrentDate
  16.                 rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
  17.                 rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
  18.                 rstSchedule![Ports] = lngPorts           'pulls from the form
  19.                 rstSchedule.Update
  20.                 fPreviousRecs = False
  21.             Else
  22.                 lngScheduleID = rstSchedule![ScheduleID]
  23.             End If
  24.         End If
  25.         Select Case Me![Type]
  26.             Case "Week In Month"            'Monthly Week In Month
  27.                 datTmp = dblCurrentDate
  28.                 dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
  29.                 intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
  30.                 If intDayNo > intWeekday Then intDayNo = intDayNo - 7
  31.                 intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
  32.                 'Handle invalid dates
  33.                 On Error Resume Next
  34.                 datTmp = CDate(intDayNo & Format(dblCurrentDate, " mmmm yyyy"))
  35.                 On Error GoTo 0
  36.                 'If setting datTmp failed then it still has last month's date in it
  37.                 If Month(datTmp) = Month(dblCurrentDate) Then
  38.                     dblCurrentDate = datTmp
  39.                 Else
  40.                     dblCurrentDate = CDate(intDayNo - 7 & _
  41.                                            Format(dblCurrentDate, " mmmm yyyy"))
  42.                 End If
  43.             Case "Date In Month"            'Monthly Date In Month
  44.                 dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
  45.             Case "Bi-Weekly"                'Bi-Weekly
  46.                 dblCurrentDate = dblCurrentDate + 14
  47.             Case "Weekly"                   'Weekly
  48.                 dblCurrentDate = dblCurrentDate + 1
  49.             Case "Daily"                    'Daily
  50.                 dblCurrentDate = dblCurrentDate + 1
  51.         End Select
  52.     Wend
Nov 30 '06 #8

P: 6
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.
Dec 1 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 1 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.