Function to return a specific day of every month (1 Viewer)

Snowflake68 · Jan 16, 2018

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.

Code:

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 · Jan 16, 2018

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

Snowflake68 · Jan 16, 2018

ridders said:
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 · Jan 16, 2018

Simply replace the 1 with the variable - something like;

Code:

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 · Jan 16, 2018

Minty said:
Simply replace the 1 with the variable - something like;

Code:

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 · Jan 16, 2018

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

Code:

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 · Jan 16, 2018

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

Code:

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:

Code:

SpecificDayInMonth([InvoiceDate],[InvDayOfMonth])

EDIT: Hadn't realised Minty had already answered

Snowflake68 · Jan 16, 2018

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

Code:

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.

Function to return a specific day of every month (1 Viewer)

Snowflake68

Registered User.

isladogs

MVP / VIP

Snowflake68

Registered User.

Minty

AWF VIP

Snowflake68

Registered User.

Attachments

Minty

AWF VIP

isladogs

MVP / VIP

Snowflake68

Registered User.

Similar threads

Users who are viewing this thread