Automatically insert the last day of the month 1

[RobPotts]

Hi,

Is there a way to make a feild enter the last day of the month when a user
just enters the month and year. Currently when Jun-02 is entered the form
returns 02-06-02, I want it to say 30-06-02.

Thanks in advance
Rob!

 

[danvlas]

DateValue(DateAdd("m",1,[DateField])-Day(DateAdd("m",1,[DateField])))
will do the trick.
Whatever date you enter in [DateField], the expression will return the last day of that date's month...


HTH,

Dan

 

[RobPotts]

Hi Dan,

Where have you put this function

Rob

 

[danvlas]

It's not really a function. You can use the expression in a query, or in a calculated control on a form.
If you feel more comfortable with it as a function, here it is:

Function LastDayOfMonth(YourDate as Date) As Date
LastDayOfMonth = DateValue(DateAdd("m",1,YourDate)-Day(DateAdd("m",1,YourDate)))
End Function

Paste it into a module, then use it whenever you want in your project just like any othe function...

Regards,

Dan

 

[RobPotts]

Hello Again,

I've put the first expression in a field and I've got it working, but I've noticed another problem. I am trying to calculate expiry feilds, yet it only returns this year. When I type Jun-05 I get 30-Jun-02. All the expiry periods are different. Is there a way I can set the date format up to recognise that I'm only entering the month and year.

Now I've created this feild how can I get it to be copied in to the expiry date feild on the table

Thanks again
Rob

 

[danvlas]

Paste this in a module

Function LastDayOfMonth(YourDate as Date)
LastDayOfMonth = DateValue(DateAdd("m",1,YourDate)-Day(DateAdd("m",1,YourDate)))
End Function

Then go to your form-design view
Paste this (but change the name of the text box accordingly):

Sub YourTextBoxName_AfterUpdate()
Me("YourTextBoxName&quot=LastDayOfMonth(Me("YourTextBoxName&quot)
End Sub

It works for me...

Regards,
Dan

 

[MichaelRed]

EOM = DateSerial(Year(Yourdate), Month(Yourdate + 1, 0)


to use in a form/report, loose the "EOM" and place the exp in the control source. Just remember to replace Yourdate with the source / location of the date value. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[danvlas]

Right...but...
EOM = DateSerial(Year(Yourdate), Month(Yourdate) + 1, 0)

Regards,

Dan

 

[105]

A follow up quesiton on this, instead of entering the last day of the month, is there a way to enter the date of the week ending day (assuming Sunday as the w/e) after user enters a date?

May

 

[danvlas]

Check thread705-335129 for week start.

In your expression:
EndWeekDay = DtCommWeek(YourDate) + 6

Change (+6) to whatever between 0 and 6 depending on what day you consider as end of week.

Regards,

Dan

 

[MichaelRed]

Public Function basWkDayDt(DtIn As Date, WkDay As Integer) As Date
    basWkDayDt = DateAdd("d", -1 * Weekday(DtIn, WkDay) + 1, DtIn)
End Function

as in:
? basWkDayDt(Date, vbFriday)
8/30/02
? basWkDayDt(Date, vbSunday)

Note the Second Argument MUST be in the range of 0 to 7 (also note that for this argument, MS Speak considers 0 = 1, which many of us have struggled with at times)

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[RobPotts]

Hi,

I'm still having trouble with this. I've entered the code by Dan, with the function and the afterupdate code. But when I try to enter a date as Jul/05, it comes back as 31/Jul/02. I need it to return 31/Jul/05. What am I missing, my code looks like:

Function LastDayOfMonth(YourDate As Date)
LastDayOfMonth = DateValue(DateAdd("m", 1, YourDate) - Day(DateAdd("m", 1, YourDate)))
End Function

And Sub:
Private Sub Expiry_Date_AfterUpdate()
Me.[Expiry Date] = LastDayOfMonth(Me.[Expiry Date])
End Sub

Also if an expiry date is entered in error then deleted I get a Runtime error 94: Invalid use of Null
and it Highlight the after update event when I debug it. Can I add comething to this code to allow for a deletion.

Thanks in advance
Rob! [Bigcheeks]

 

[MichaelRed]

In the call, the date is of date type, but Jul/01 doesn't evaluate to a date. You COULD do the call with DateValue("Jul/01&quot which evaluates to #7/1/02#. For the "Null" error, I would suggest trapping BEEFORE doing the date calc (call to LastDayOfMonth) and have a msgbox inform the user that the value MUST be filled in.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places