MS Access - Date Field change to end of month

Amour22015
Amour22015 used Ask the Experts™
on
Hi Experts,

I have this query in Access:
INSERT INTO NOAH2_tblNAsAffiliations ( Contact1, Contact2, Affiliation, Address, Date1, Date2, Date5, Date6, Code1, Code2, Code5, Cntr3, Line1, Memo1, Memo2, AcctCo, Assn )
SELECT USER_dbo_certificates.CompanyID, v_inttblNAsContactsINDIVIDUAL.Contact, "3A" AS Expr1, v_inttblNAsContactsCOMPANY.KeyAddressID, USER_dbo_certificates.CreateDateTime, USER_dbo_certificates.ExpireDate, USER_dbo_certificates.IssueDate, USER_dbo_certificates.NextTPVMonthYear, "3A" AS Expr3, USER_dbo_certificates.Standard, USER_dbo_certificates.Status, USER_dbo_certificates.CertificateNo, "TBD" AS Expr4, USER_dbo_certificates.ModelDesignations, USER_dbo_certificates.Notes, "3A" AS Expr6, "3A" AS Expr7
FROM (USER_dbo_certificates INNER JOIN v_inttblNAsContactsCOMPANY ON USER_dbo_certificates.CompanyID = v_inttblNAsContactsCOMPANY.Contact) LEFT JOIN v_inttblNAsContactsINDIVIDUAL ON USER_dbo_certificates.AdminContactID = v_inttblNAsContactsINDIVIDUAL.UserID2;

Open in new window


The Date field is:
[NextTPVMonthYear]

The date is currently:
NextTPVMonthYear
5/1/2016
3/1/2016
10/1/2017
10/1/2018
2/1/2017
9/1/2016
10/1/2017

I need the dates when Inserted into tblNAsAffiliations to be the last day of the month so like:
NextTPVMonthYear
4/30/2016
2/30/2016
9/30/2017
9/30/2018
1/30/2017
8/30/2016
9/30/2017

So something like:
(Date([NextTPVMonthYear], -1)

But I know this is not the correct syntax.

Please help and thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014

Commented:
Private Function LastDateMonth(InDate as Date) as Date

Dim TheMonth As Integer
Dim TheYear As Integer
Dim TheDate As Date

If IsDate(InDate) = False Then
    MsgBox "not a date"
    Exit Sub
End If

TheMonth = Month(InDate)
TheYear = Year(InDate)
If TheMonth = 12 Then
    TheMonth = 1
    TheYear = TheYear + 1
Else
    TheMonth = TheMonth + 1
End If
TheDate = DateAdd("d", -1, DateSerial(TheYear, TheMonth, 1))
LastDateMonth = TheDate

End Function

Author

Commented:
I am not looking for this in VBA.

I need this within my query that I posted.

Thanks for helping.
Top Expert 2016
Commented:
try this syntax


dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Use DateAdd to subtract one day:
INSERT INTO
NOAH2_tblNAsAffiliations
 ( Contact1, Contact2, Affiliation, Address, Date1, Date2, Date5, Date6, Code1, Code2, Code5, Cntr3, Line1, Memo1, Memo2, AcctCo, Assn )

SELECT
USER_dbo_certificates.CompanyID, v_inttblNAsContactsINDIVIDUAL.Contact, "3A" AS Expr1, v_inttblNAsContactsCOMPANY.KeyAddressID, USER_dbo_certificates.CreateDateTime, USER_dbo_certificates.ExpireDate, USER_dbo_certificates.IssueDate, DateAdd("d", -1, USER_dbo_certificates.NextTPVMonthYear), "3A" AS Expr3, USER_dbo_certificates.Standard, USER_dbo_certificates.Status, USER_dbo_certificates.CertificateNo, "TBD" AS Expr4, USER_dbo_certificates.ModelDesignations, USER_dbo_certificates.Notes, "3A" AS Expr6, "3A" AS Expr7
FROM (USER_dbo_certificates INNER JOIN v_inttblNAsContactsCOMPANY ON USER_dbo_certificates.CompanyID = v_inttblNAsContactsCOMPANY.Contact) LEFT JOIN v_inttblNAsContactsINDIVIDUAL ON USER_dbo_certificates.AdminContactID = v_inttblNAsContactsINDIVIDUAL.UserID2;
Most Valuable Expert 2014

Commented:
I am not looking for this in VBA.
 I need this within my query that I posted.
 Thanks for helping.


Make it a Public Function in a module.
Then call it in the Query
NextTPVMonthYear: LastDateMonth([WhatEverFieldIsPresentlyTheDate])

You can call Public Functions from queries.
Top Expert 2016

Commented:
this expression


dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)


will give you the last date of the previous month of the value in [NextTPVMonthYear]
even the day is not the first day.
6/1/2015  > 5/31/2015
6/30/2015 > 5/31/2015

etc...
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Nick, you are missing the power of DateSerial:
Public Function DatePreviousMonthLast( ByVal datDateThisMonth As Date) As Date

    DatePreviousMonthLast = DateSerial(Year(datDateThisMonth), Month(datDateThisMonth), 0)

End Function

Open in new window

/gustav
Most Valuable Expert 2014
Commented:
I've never thought of DateSerial that way, but clearly it can be used with math and therefore DateAdd() isn't necessary.  Neat!

From the Access 2003 help
The following example uses numeric expressions instead of absolute date numbers. Here the DateSerial function returns a date that is the day before the first day (1 - 1), two months before August (8 - 2), 10 years before 1990 (1990 - 10); in other words, May 31, 1980.

DateSerial(1990 - 10, 8 - 2, 1 - 1)

Elegant.  It's not intuitive that 0 for the day argument should return the last day of the previous month, but it does.
Top Expert 2016

Commented:
you don't need a function to use the dateserial()

it will work directly in your query

Author

Commented:
On:
dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)



This would work for me because all the clients dates are starting on the first day of the month.  But if this:
6/30/2015 > 5/31/2015 it would not work and have to go to 6/31/2015.

Thanks for all your help.

Author

Commented:
Thanks for all your help.  I decided to use:
DateAdd("d", -1, USER_dbo_certificates.NextTPVMonthYear)
But may use:
dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)
at another time.
Top Expert 2016

Commented:
<6/30/2015 > 5/31/2015 it would not work and have to go to 6/31/2015.>

there is no date  6/31/2015 in the calendar

Author

Commented:
So I mean than in that case I could not use:
<6/30/2015 > 5/31/2015

it would have to be:
<6/30/2015 > 6/30/2015

I am only looking for the last day of the month.  I know if client had:
<6/30/2015
Then this:
DateAdd("d", -1, USER_dbo_certificates.NextTPVMonthYear)
would have given me:
<6/29/2015
Thankfully the client had all dates starting on the first day of the month.
So this:
DateAdd("d", -1, USER_dbo_certificates.NextTPVMonthYear)
Or
dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)
Would have worked and that is why I gave you a:
150

Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial