asked on
MS Access - Date Field change to end of month
Hi Experts,
I have this query in Access:
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
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;
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
Last Comment
Amour22015
ASKER
I am not looking for this in VBA.
I need this within my query that I posted.
Thanks for helping.
I need this within my query that I posted.
Thanks for helping.
SOLUTION
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.
ASKER CERTIFIED SOLUTION
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.
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([WhatEverFie
You can call Public Functions from queries.
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([WhatEverFie
You can call Public Functions from queries.
this expression
dateserial(year([NextTPVMo
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...
dateserial(year([NextTPVMo
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
SOLUTION
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.
you don't need a function to use the dateserial()
it will work directly in your query
it will work directly in your query
ASKER
On:
dateserial(year([NextTPVMo
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.
dateserial(year([NextTPVMo
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.
ASKER
Thanks for all your help. I decided to use:
DateAdd("d", -1, USER_dbo_certificates.Next
But may use:
dateserial(year([NextTPVMo
at another time.
DateAdd("d", -1, USER_dbo_certificates.Next
But may use:
dateserial(year([NextTPVMo
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
there is no date 6/31/2015 in the calendar
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.Next
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.Next
Or
dateserial(year([NextTPVMo
Would have worked and that is why I gave you a:
150
Thanks for your help.
<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.Next
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.Next
Or
dateserial(year([NextTPVMo
Would have worked and that is why I gave you a:
150
Thanks for your help.
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