Criteria last tuesday of the next month (1 Viewer)

[robina]

I have read through all the date threads I could find but didn't see a solution. I need my query criteria to be >the last tues of next month. How would I write this?

Thank you.

 

[plog]

I would make it a function using this code:

Function getNextMonthLastTuesday() As Date
    ' returns date of last tuesday of next month
Dim ret As Date
ret = DateSerial(Year(Date), Month(Date) + 2, 0)
    ' gets date of last day of next month

daystosubtract = Weekday(ret)
If (daystosubtract >= 3) Then daystosubtract = daystosubtract - 3 Else daystosubtract = daystosubtract + 4
    'determines how many days prior to ret that Tuesday occured, will be subtracted from ret
    
getNextMonthLastTuesday = DateAdd("d", -1 * daystosubtract, ret)

End Function

Then use getNextMonthLastTuesday() to return the value wherever you need to use it.

 

[robina]

I get the "Undefiend function in expression error when the query runs.

 

[bob fitz]

Where have you put the functions code. It needs to be in a general module, not in a forms module.

 

[robina]

I put it in a general module. I've attached a screen shot. thank you.

 

[robina]

I figured it out. The module name cannot be the same as the function name. It works now. thank you.