After Update property

  • Thread starter Thread starter Lady_Dee
  • Start date Start date
L

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 .
 
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 .
Click to expand...

Have some patience.
Asked in a different thread just 30 minutes earlier and answered
there.
 
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.
 
Lady_Dee said:
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 .
Click to expand...

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.
 
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
Click to expand...

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.
 
Rick said:
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.
Click to expand...

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
 
Daniel said:
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.
Click to expand...

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
 
Back
Top