Return list of dates with same day of month

[Happy YN]

I refer to the code in the code repository under this heading

Return list of dates with same day of month

I have tried for hours to make this work but I keep on getting a type mismatch (13) error.
It is really exactly what I'm desperately looking for so I'd appreciate if anyone who has tried it and got it working to let me know - Thanks
(One may not post a reply in that section of the forum if I understand correctly)
Alternatively if you know of any alternative code please let me hear it! Thanks

 

[sonny]

This code creates a temp tbl with the all dates between the start and end date entered.

Private Sub btnOK_Click()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim v As DAO.Field
    Dim i As Index
    Dim tNamePresent As Boolean
    Dim TableExists As Boolean
    Dim DateI As Date
    Set db = Application.CurrentDb

    If IsNull(Me.txtFirstDate) Then
        MsgBox "You need a Starting Date!"
        Exit Sub
    End If
    If IsNull(Me.txtSecondDate) Then
        MsgBox "You need a Ending Date!"
        Exit Sub
    End If

    Const strTableName = "TemptblLeave" 'the name of your temp table
    ' delete table if already exists
    For Each tdf In db.TableDefs
        If tdf.Name = strTableName Then
            db.TableDefs.Delete strTableName
            Exit For
        End If
    Next
    'make table
    Set tdf = db.CreateTableDef(strTableName)
    Set v = tdf.CreateField("LeaveDate", dbDate) 'The new field name
tdf.Fields.Append v
   'append table to database
    db.TableDefs.Append tdf


    Set rst = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)

    For DateI = CDate(Me.txtFirstDate) To CDate(Me.txtSecondDate)
        With rst
            .AddNew
            .Fields("LeaveDate") = Format(DateI, "mm/dd/yyyy")
            .Update
        End With
    Next DateI
    Set rst = Nothing
    RefreshDatabaseWindow
End Sub

 

[sonny]

FYI, all you need is a form with 2 unbound text boxes called txtFirstDate & txtSecondDate and a cmd button called btnOK. Put this code OnClick event of the cmd button.....

 

[Happy YN]

Thats good thanks but I wanted one step further namely that it would only give you a certain day of the month e.g 15th.
Moreover it was clever enough to calculate the last day ofeach month and to tackle situations where the day didn't exist that month e.g 30th for Feb
Did you look at that code I referred to?
Thanks again

 

[sonny]

'LastDayOfWeekInMonth
'Calculates the last day (Sunday, Monday, etc.) of the month

'Declaration
Private Function LastDayOfWeekInMonth( _
intMonth As Integer, _
intYear As Integer, _
eDayOfWeek As VBA.VbDayOfWeek) As Date

'Arguments
intMonth - A number between 1 and 12 indicating the month of the year to check
intYear - The year to check
eDayOfWeek - One of the VBA day of week constants (vbSunday, vbMonday etc.)

'Returns
The last occurrence of the specified day in the month

 

[Happy YN]

Sorry there is a misunderstanding. I meant the last date in the month. e.g. 30, 31 or 28. So if a user specified they wanted a transaction on the 29th of each month, it would know that on feb it should do it on the 28th. Also you did not respond to the point that I want a list of specific date in the month i.e return all the 15th between those two dates. of course one could write code to filter the temp table but then if the problem of 31st came up, it just would not list the April date for example. If you read the link I posted you will see what I want!
I appreciate your help and am not demanding ! just clarifying what I am looking for
A grateful Happy YN

 

[Bat17]

Happy
Do you need to rake account of Weekend or public holidays when calculating the dates?

Peter

 

[Bat17]

Looking at your link I think I see where your problem lays. The function is returning an array of date serials.
You dont say hao you are using the returned array but at a guess you are trying to print it and that is when you are getting the error.

here is a code sample that will print out the dates to the immediate window (Ctrl-G)

Sub testit()
Dim aDates
Dim j As Integer
aDates = fncDayOfMonthDates(#4/15/2004#, #2/1/2005#, 31)
For j = 0 To UBound(aDates)
Debug.Print Format(aDates(j), "dd mmmm yyyy")
Next j
End Sub

HTH

Peter

 

[Happy YN]

Thanks for that and yes I tried it and it works. What I was trying to do was what to use the fuction 'subTestDayOfMonthDates' brought there which claims to print out to the immediate window. I guess I don't really understand what an array really is. I thought it would be a string of dates seperated by a comma.
Anyway if I would have to do something with each date I would have to include that action before each 'next'
Thanks again for this
A happier YN

 

[mresann]

I'm sorry, I was away for a couple of months.

I see you solved the issue with the subroutine and function difference. But I would not alter the function itself if I were you. Just loop through the array that is returned from the function to process your dates.

 

[Happy YN]

Thanks for getting back to me. as you can see Bat17 gave me some guidance in using the function. I had tried it in the immediate window with errors that I had reported. I still don't fully understand how something is stored as an array and how it is handled?

 

[workmad3]

O.k. This is probably too simplistic for you, and i apologise, but its the only way that i can think of that makes sense for explaining arrays.

Think of your computers memory as a series of locations that can store what you want. Without arrays, each one is given an individual name and accessed individually. Using arrays, you can give lots of consecutive locations just one name, and you can then access them using just this one name, and an offset (i.e. date[1] means access the first item, date[200] means access the 200th item) This allows a lot of powerful things to be done with arrays (such as returning them as a function result.... equivalent to returning several hundred variables as a function result... not normally possible) and also they can be used in loops to loop through every item in the array and perform some action on each.