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
(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