MS Access - Date Field change to end of month

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
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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
Amour22015Author Commented:
I am not looking for this in VBA.

I need this within my query that I posted.

Thanks for helping.
Rey Obrero (Capricorn1)Commented:
try this syntax


dateserial(year([NextTPVMonthYear]),month([NextTPVMonthYear]),0)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LEARN MORE
Gustav BrockCIOCommented:
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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
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.
Rey Obrero (Capricorn1)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...
Gustav BrockCIOCommented:
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
Nick67Commented:
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.
Rey Obrero (Capricorn1)Commented:
you don't need a function to use the dateserial()

it will work directly in your query
Amour22015Author 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.
Amour22015Author 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.
Rey Obrero (Capricorn1)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
Amour22015Author 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.