Link to home
Start Free TrialLog in
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
Avatar of Nick67
Nick67
Flag of Canada image

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
Avatar of Amour22015
Amour22015

ASKER

I am not looking for this in VBA.

I need this within my query that I posted.

Thanks for helping.
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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...
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you don't need a function to use the dateserial()

it will work directly in your query
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.
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.
<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
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.