Set Last Day of Previous Month 3

[ahg]

I have a date field that I want to set to display the last day of the previous month when I select a check box. Can anyone give me the code for this please?

Many thanks in advance,

Tony

 

[cmmrfrds]

It is typically done by setting the first day of the month after the previous month and then subtract 1. For example the end of June is.

yourdate = "7/1/02"
datepart("d","7/1/02&quot - 1
DateAdd("d", -1, yourdate)

gives 6/30/02

 

[ahg]

Thanks, but not sure how to write this so that when I select the check box today (on Nov 17th) the field I wish to set the date in will read October 31st?

Thank again. I know it sounds easy but confusion reigneth!

 

[cmmrfrds]

You could put the code in the lost focus or after update event on the check box.

Sub LostFocus()
Dim currYY as string, currMM as string

currYY = datepart("y",Now()) '- this would be current year
currMM = datepart("m",Now()) '- this would be current month

Me.yourDate = currMM & "/1/" & currYY
Me.yourDate = DateAdd("d", -1, yourDate)

End Sub

 

[ahg]

Again, many thanks - but when I apply this I get the date Oct-31-321 instead of Oct-31-2002. Where would I be going wrong?

 

[danvlas]

Date()-Day(Date())
will give you the last day of previous month

If you have a date field:

DateField - Day(DateField)

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[ahg]

Many thanks Daniel, That works great - so simple, yet elegant!

Tony

 

[MichaelRed]

A somewhat more generic version:

MyDate = #12/25/02#
? DateSerial(Year(MyDate), Month(MyDate), 0)
11/30/02

Where (obviously) "MyDate" can be any valid date. Replacing the "0" with "1" (again so obviously) will return the date of the first of the month of "MyDate". There are so many variations of this that it is tedious to even begin to list them. The interested user may discover some way to return almost any logically derivablle date from some elaboration and variation.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cmmrfrds]

Sorry about the wrong parameter. The single y was the day since the beginning of the year i.e. 321.

currYY = datepart("yyyy",Now())

 

[ahg]

Thanks all for your help - only goes to show the flexibility of Access and VB!

Tony

 

[happest]

For the previous month first - last day

Text0 = "'" & (Date - Day(Date)) - (Day(Date - Day(Date)) - 1) & " - " & Date - Day(Date) & "'"
Jason H
Database Admin & Program Developer

http://www.LuckyEagleCasino.com