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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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.Comp
FROM (USER_dbo_certificates INNER JOIN v_inttblNAsContactsCOMPANY
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.Comp
FROM (USER_dbo_certificates INNER JOIN v_inttblNAsContactsCOMPANY
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
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
Elegant. It's not intuitive that 0 for the day argument should return the last day of the previous month, but it does.
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.
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.
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial