Show date for a particular day between 2 dates

[Tadynn]

Hi all,


Access 2K

Just looking for ideas or suggestions on how to do this one. I need to find out how to create a module that will calculate how many times a particular day shows up between two different dates.

Logic:

StartDate: 01/01/2004
FinishDate: 03/02/2004
DaysToCount: All mondays

Results:
5 Mondays counted
Dates 5/01/2004, 12/01/2004, 19/01/2004, 26/01/2004, 2/02/2004.

The dates from the results I would like to create a table (tbl_DatesTemp) and insert the results into.

Will a standard make table (SELECT [TableName].[Fields] INTO [tbl_DatesTemp] From [?]) work even though I don't have a FROM table for this last part? I'm assuming not.


Thanks, Tadynn

 

[SteveR77]

Tadynn,

You are correct the make table will not work without a data source for the query to create the new table from.


Steve

 

[MinusM]

'"SELECT " & value1 & " AS Field1, " & value2 & " AS Field2 INTO tbl_DatesTemp"
'
'The above SQL will create a table of 2 fields with one row
'The SQL below will append to existing table
'
'"INSERT INTO tbl_DatesTemp
'SELECT " & value1 & " as Field1," & value2 & " as Field2"
'
'
'For counting the number of WeekDays the best logic I feel is to find the first day which is Monday
'and use Datediff function

FirstDate = DateAdd("w", 7 - DatePart("w", Date1) + ReqDay, Date1)
'where ReqDay is one of vbSunday ,vbMonday ....
'Then
DaysToCount = DateDiff("w", FirstDate, Date2)
'if you want all the dates you can loop Daystocount-1 no of times and use DateAdd("d",7,NewDate)
'
'Note From Access HELP
'When interval is Weekday ("w&quot, DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1

 

[MichaelRed]

Not sure I would actually create a table of the dates, as this will contribute to the growth (bloat) of the .mdb. If you use ADO, you can create a recordset from many sources (including arrays), so I'v stopped at the point of generating the set ov values. Taking small steps is probably safer / easire than jumping directly into the dep end:

Please read and heed!!! In all of the following, niceties such as error checking is omitted. Adoptrors should CAREFULLY consider the inclusion of such checks, and do some testing to see what abnormal situations CAN occur before incorporating any part. Since I do not know (or much care) about the more general environment where these functions might be used, error checking by me could easily be as disruptive as helpful. Some may want any possible errors checked at a higher level, while a different perspective may be employed elsewhere.


First, just a simple function to return the date of teh next specified day of the week following some date:

Public Function basNxtWkDay(Optional dtSt As Variant, _
                            Optional DayOfWeek As Integer = vbSunday) As Date

    'Michael Red    12/14/2002 Tek-tips thread222-427748;  Modified to Incl [dtSt], 1/24/04


    Dim DtStrt  As Date
    
    If (IsMissing(dtSt)) Then
        DtStrt = Date
     Else
        DtStrt = dtSt
    End If

    DtStrt = (DtStrt + 7)
    DtStrt = DtStrt - (Weekday(DtStrt, DayOfWeek) - 1)

    basNxtWkDay = DtStrt

End Function

Then, a function to return an ARRAY of dates for a sprcified Day of the Week between two dates:

Public Function basMkDtAry(dtSt As Date, dtEnd As Date, DOW As Integer) As Date()

    Dim MyMons() As Date
    Dim Idx As Long

    ReDim MyMons(0)
    MyMons(0) = basNxtWkDay(dtSt, DOW)

    If (Weekday(dtSt) = DOW) Then
        MyMons(0) = dtSt
     Else
        MyMons(0) = basNxtWkDay(dtSt, DOW)
    End If

    Do While MyMons(Idx) <= dtEnd

        If (MyMons(Idx) + 7 <= dtEnd) Then
            ReDim Preserve MyMons(UBound(MyMons) + 1)
            MyMons(UBound(MyMons)) = MyMons(Idx) + 7
          Else
            Exit Do
        End If

        Idx = Idx + 1
    Loop

    basMkDtAry = MyMons()

End Function

and finally a SIMPLE test program to illustrate the use:

Public Function basTstMkDtAry(dtSt As Date, dtEnd As Date, DOW As Integer)

    'Sample Usage
    '? basTstMkMon(#1/1/04#, #2/1/04#, vbMonday)
    '1/5/04
    '1/12/04
    '1/19/04
    '1/26/04

    Dim Idx As Integer
    Dim MyDt() As Date

    MyDt() = basMkDtAry(dtSt, dtEnd, DOW)

    While Idx <= UBound(MyDt)
        Debug.Print MyDt(Idx)
        Idx = Idx + 1
    Wend

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MARELI]

Hello to you!

i saw your answer and i wondered if i can do somthing like your function but with time. i m new here in this forum but months ago i realy tried to find function like you did so elagant.

the aim of the function i m looking for is to insert a time (with format "hh:mm&quot in the correct place.

i have 9 fields like that format and i want, after inserting one value into it and GotFocus on the other, that the function take place and fill this control with a value that is not (equal+30 minutes) to any of other controls that ixisisting already in thos 9 controls.

I hope you can advice me sir,

thanks in advance

MARELI

 

[MichaelRed]

[coclor blue]MARELI[/color],

Sorry, I do not at all understand what you want. You never specify what 'value' you want, only what you DO NOT want. While avoiding a value simply means comparing it to the possible matches, actually setting it requires an explicit value.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MARELI]

Hi MichaelRed !

Sir,

Sorry, You absulotly right: i was not clear
i'll try again with my poor english:

all the 9 fields are the starting time that student takes a course in someday:
he has a schdule for this day and the secretary has to fill the starting times for each course like this:

English 08:30
English 11:20
English 10:40
.
Biology --:--
.
.
Biochemistry 09:50

now, she wants to add Biology
i dont want her to spend time to look for when Biology time can be set, i need a function that looking for the time in this day schdule, that not conflict with other times and leaves 30 minutes from eache starting time .

i thanks you very much in advance sir.

MARELI

 

[MichaelRed]

Still incomplet. 30 minutes from the starting time implies that the course/subject is schedualed for less than 30 minutes (course time + travel time <= 30 minutes) one would need to know the course ending time (or duration). Further, each course is probably NOT schedualed for every 30 minute (possible time), so you need to know the available starting times for each course. Also, the individual course sections are probably limited in the enrollment allowed, so you would nr=eed to have some indication of 'space availability in the specific section.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BSman]

I wrote a long answer that got lost, so I'm going to write a quick summary of what to do without actually writing any VBA code.

Your problem of counting the number of times a weekday occurs actually is fairly simple to solve. Just take these steps:

1. Count the number of days between the two dates.
2. Divide the result by 7. The integer answer is the minimum number of times the weekday occurs, since it occurs once each week.
3. Take the number of remaining days (less than 7), I'll call it &quot;Remain&quot;.
4. Determine the number of the weekday for the first date in your time frame.
5. Add the number of the weekday to find to the weekday number of the first date + 7, then divide by 7 and determine the remaining number (7 or less).
6. If Remain is equal to or less than the number calculated in step 5, add one to the number from 2.

Now you know the number of times the weekday occurs.