After Update property

[Lady_Dee]

After entering date in one field, I need another field to reflect the end of
the month that I attached the update expression to. example, if I enter
3/10/09 in the field, I want the other field to automatically enter 3/31/09
in the field .

 

[fredg]

After entering date in one field, I need another field to reflect the end of
the month that I attached the update expression to. example, if I enter
3/10/09 in the field, I want the other field to automatically enter 3/31/09
in the field .

Have some patience.
Asked in a different thread just 30 minutes earlier and answered
there.

 

[Daniel Pineault]

There are different ways you could do this, but in this case you need not
reinvent the wheel. Simply use the code found at

http://www.mvps.org/access/datetime/date0007.htm

Specifically you are interested in the LastOfMonth() function.

Then you would use the after update event something like

Me.YourOtherControlName = LastOfMonth(#3/10/09#)
or
Me.YourOtherControlName = LastOfMonth(Me.YourControlName)
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Hans Up]

After entering date in one field, I need another field to reflect the end of
the month that I attached the update expression to. example, if I enter
3/10/09 in the field, I want the other field to automatically enter 3/31/09
in the field .

You could use the DateSerial to determine the first day of the next
month and subtract one day from that. Here's a function to do that
for you:

Public Function lastDayOfMonth(ByVal pdteIn As Date) As Date
lastDayOfMonth = DateSerial(Year(pdteIn), _
Month(pdteIn), 1) - 1
End Function

Then in the AfterUpdate event of OneField, you can say:

Me.OtherField = lastDayOfMonth(Me.OneField)

However, could OneField ever be Null after update? (That
function will cause an error, "Invalid use of null.") What
would you want to happen in that situation?

I doubt you need to store the calculated value for
OtherField in a table. Just calculate it whenever you
need it.

 

[Rick Brandt]

You could use the DateSerial to determine the first day of the next
month and subtract one day from that. Here's a function to do that for
you:

Public Function lastDayOfMonth(ByVal pdteIn As Date) As Date
lastDayOfMonth = DateSerial(Year(pdteIn), _
Month(pdteIn), 1) - 1
End Function

No need to subtract one from the DateSerial() result. Just use zero for
the final argument instead of one. The zeroth of a month is the last day
of the prior month as far as DateSerial() is concerned.

 

[Hans Up]

No need to subtract one from the DateSerial() result. Just use zero for
the final argument instead of one. The zeroth of a month is the last day
of the prior month as far as DateSerial() is concerned.

I didn't realize DateSerial was that smart! Thanks to both you and
John. Re-inventing this particular wheel was a useful exercise (for
me).

Hans

 

[Hans Up]

There are different ways you could do this, but in this case you need not
reinvent the wheel. Simply use the code found at

http://www.mvps.org/access/datetime/date0007.htm

Specifically you are interested in the LastOfMonth() function.

On my Access 2003 system, "Debug.Print LastOfMonth(Null)" gives
me "Invalid use of null", which is not what I think the author
intended. Changing the parameter from "InputDate As Date"
to "InputDate As Variant" make it behave as I want.

I submitted that suggestion through the site's Feedback link.

Now I'm curious if anyone would comment on the value of
declaring (and storing values to) the D, M, and Y variables.
D is not even used. But, for example, I don't see an
advantage to Y over just using Year(InputDate) directly
where it's needed.

Thanks,
Hans