[depricated]

So I'm working on setting up a query that checks if it's the (1st-5th weekday) of a month, or the last weekday of a month. Function is below.

I'm setting this up in the query design as expression DayOfMonth: DateSerial(Year(Date()), Month(Date()), Day(Date())) and calling it by feeding it two fields that contain strings (1st, 2nd, 3rd, 4th, 5th, Last, and Sunday-Saturday)

The function returns a Date, that is built with DateSerial

Public Function NthDayOfMonth(week As String, dayOfWeek As String) As Date
   
    'if this goes off on Friday and it's not Friday, don't even bother
    If WeekdayName(weekday(Date)) = dayOfWeek Then
    
        
        Dim nthDay As Integer
        
        If week = "1st" Then nthDay = 1
        If week = "2nd" Then nthDay = 2
        If week = "3rd" Then nthDay = 3
        If week = "4th" Then nthDay = 4
        If week = "5th" Then nthDay = 5
        If week = "Last" Then
            NthDayOfMonth = FindLastDay(dayOfWeek)
            Exit Function
        End If
        
        Dim targetDate As Date
        
        For i = 0 To 6
                If WeekdayName(weekday(DateSerial(Year(Date), Month(Date), 1 + i))) = dayOfWeek Then targetDate = DateSerial(Year(Date), Month(Date), (1 + i) + (7 * (nthDay - 1)))
        Next
        
        NthDayOfMonth = targetDate
        MsgBox NthDayOfMonth
    End If
    
End Function

Public Function FindLastDay(weekday As String)
    'I'll get to this shortly
End Function

The complicated part of this is determining the date I think, and it seems to be doing that correctly. I go to the first of the month and compare the weekday name - if it finds the day it then adds 7*(weeks-1). It appears to be functioning correctly and returning the correct dates...

however, after if returns I get the error "Data type mismatch in criteria expression."

If I take the criteria out, it doesn't error. I've attempted casting the type of both to date, string, double, int, long, etc - and casting them to the same type doesn't even seem to do the trick.

My criteria looks something like this
Day(NthDayOfMonth([tblTasks]![monthlyEvery],[tblTasks]![monthlyWeekday])
(note: edited this because I've been trying different type casting on both the expression and criteria, and casting them to the same type still doesn't help

Any ideas? Thanks!

This post has been edited by depricated: 04 January 2013 - 11:37 AM

[andrewsw]

Could you explain again what your function is intended to return?

[andrewsw]

In the meantime, this may help: it returns the last Monday, or Tuesday, etc., of the (current) month:

'Usage: ?lastOfMonth("Thursday")
'Date value returned: 31/01/2013

Function lastOfMonth(strDay As String) As Date
    'of the current month
    Dim dteLast As Date
    dteLast = DateSerial(Year(Date), Month(Date) + 1, 0)
    Do While WeekdayName(Weekday(dteLast, vbMonday), , vbMonday) <> strDay
        dteLast = dteLast - 1
    Loop
    lastOfMonth = dteLast
End Function

It uses the fact that the zeroth day of a month is the first of the previous month.

[depricated]

Thanks! It's intended to return a Date

Right now to simplify what I should be outputting is this:

targetDate = DateSerial(Year(Date), Month(Date), (1 + i) + (7 * (nthDay - 1)))

NthDayOfMonth = targetDate

Also, if I take out the criteria and set the function as used in the criteria to be the expression and append it into a table, it comes out correct (1/4/2013) - it stores correctly in a Date Field even.

[depricated]

Got it working but it feels like a kludge.

Thanks, by the way, for the LastDayOfMonth code I'm going to readdress this Monday when I get back in to work . . . but what I'm doing is a weird..pseudo-boolean.

Apparently I have to have data in all the cells it's checking. This hasn't been true for other criteria.

So I redid the table so that monthlyEvery and monthlyWeekly are appropriate integers. I have them default to 0, and if the function receives 0 it returns a larger number (which then fails comparison). I check the calculated date against the current date and if they match I return the received monthlyEvery (which I'm matching on, this succeeds the match) and if not return a large number that will fail the match.

Then I have to strictly type cast everything to CInt, though it already is. Oi, it's ugly to look at it, but it works.

Here's the code I wound up using in the end.

Public Function NthDayOfMonth(week As Integer, dayOfWeek As Integer) As Integer
    
    'if this goes off on Friday and it's not Friday, don't even bother
    If Not IsNull(week) And Weekday(Date) = dayOfWeek Then
        
        Dim nthDay As Integer
        
        If week = 6 Then
            NthDayOfMonth = FindLastDay(dayOfWeek)
            Exit Function
        End If
        
        If week = 0 Then
            NthDayOfMonth = 42
        End If
        
        Dim targetDate As Date
        
        For i = 0 To 6
            If Weekday(DateSerial(Year(Date), Month(Date), 1 + i)) = dayOfWeek Then targetDate = DateSerial(Year(Date), Month(Date), (1 + i) + (7 * (week - 1)))
        Next
        
        If targetDate = DateSerial(Year(Date), Month(Date), Day(Date)) Then
            NthDayOfMonth = week
        Else
            NthDayOfMonth = 37
        End If
    End If
    
End Function

[andrewsw]

week As Integer, dayOfWeek As Integer

Yes, if you are passing field-values to your function then they must ALL be of the correct type: two integers.

You could use an IIF in your criteria to convert all null, or empty, values to an Integer (0) before passing them to your function. Then, in your function, return some default value if it receives zeroes. [Note: When using a VBA function in an Access query you must ensure that the return value(s) are all of the same type.]

You could change Integer to Variant in your function but THIS IS NOT THE RECOMMENDED APPROACH: I advise against it. Use the above approach to ensure that you function receives arguments of the correct type.

BTW Access has a function NZ() to convert nulls to zero. Don't use this! It causes problems further down the line. Use IIF([field] Is Null,0,[field]) instead.

Possibly:

IIF([field] Is Null Or IsEmpty([field]),0,[field])

This post has been edited by andrewsw: 04 January 2013 - 02:12 PM