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