Avatar of Amour22015
Amour22015
 asked on

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
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Amour22015

8/22/2022 - Mon
Nick67

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
Amour22015

ASKER
I am not looking for this in VBA.

I need this within my query that I posted.

Thanks for helping.
SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Nick67

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rey Obrero (Capricorn1)

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 Brock

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
SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

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

it will work directly in your query
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amour22015

ASKER
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.
Amour22015

ASKER
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)

<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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Amour22015

ASKER
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.