Function to return a specific day of every month

[Snowflake68]

I have a sales table with one of the fields is an invoice date. I need to run a query that will find the 30th of every month in the invoice date field.

I have a function that will find the first day of the month but cannot find anything that will find a specific day of every month.

This is my function for the first day of the month so not sure if I can use this to help with my solution or not.

Public Function FirstDayInMonth(Optional dtmDate As Variant) As Date
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    FirstDayInMonth = DateSerial( _
     Year(dtmDate), Month(dtmDate), 1)
End Function

Hope someone can help.

 

[isladogs]

Replace 1 with 30 in your function.
What about February?

 

[Snowflake68]

Replace 1 with 30 in your function.
What about February?

It was just as an example but I would need to also ensure I could use a variable for the specific day of the month as that is stored in the database. So for example the field called 'InvDayOfMonth' would need to be reference to find the Specific day of the month for the given invoice date.

Would you also be able to advise me how to amend the function so that it includes that variable please?

 

[Minty]

Simply replace the 1 with the variable - something like;

Public Function SpecificDayInMonth(Optional dtmDate As Variant, iDay as Integer) As Date
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    SpecificDayInMonth= DateSerial( Year(dtmDate), Month(dtmDate), iDay)
End Function

Then to call it use to get the 6th of a month for instance;
SpecificDayInMonth([YourDateField], 6)

 

[Snowflake68]

Simply replace the 1 with the variable - something like;

Public Function SpecificDayInMonth(Optional dtmDate As Variant, iDay as Integer) As Date
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    SpecificDayInMonth= DateSerial( Year(dtmDate), Month(dtmDate), iDay)
End Function

Then to call it use to get the 6th of a month for instance;
SpecificDayInMonth([YourDateField], 6)

I have created a new function but the first line goes red and then when I run the debug it points at the iDay on the first line saying Expected: Optional

I have no idea what this means, can you help me again please?

 

[Minty]

Sorry I didn't test that - this removes the Optional,

Public Function SpecificDayInMonth(dtmDate As Variant, iDay As Integer) As Date
    
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    SpecificDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate), iDay)

End Function

 

[isladogs]

Close ....remove optional as you want to reference a specific invoice date

Public Function SpecificDayInMonth(dteDate As Date, iDay as Integer) 
    
    SpecificDayInMonth= DateSerial( Year(dteDate), Month(dteDate), iDay)
End Function

Example usage: SpecificDayInMonth(#9/7/2017#,10) gives 10/07/2017 (UK date format) or 7/10/2017 US format

So in your query add a field similar to:

SpecificDayInMonth([InvoiceDate],[InvDayOfMonth])

EDIT: Hadn't realised Minty had already answered

 

[Snowflake68]

Sorry I didn't test that - this removes the Optional,

Public Function SpecificDayInMonth(dtmDate As Variant, iDay As Integer) As Date
    
    If IsMissing(dtmDate) Then
        dtmDate = Date
    End If
    
    SpecificDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate), iDay)

End Function

Thank you so much you are a genius. All working perfectly now thank you.