Microsoft Access

[Lady_Dee]

I am trying to create an expression in the after update property of a date
field that would enter the end of the month of the date entered in the field.
example, I enter 3/5/09, I need the update field to result in 3/31/09. Need
help

 

[tina]

try adding the following to the code to the AfterUpdate event procedure, as

If IsNull(Me!DateFieldName) Then Exit Sub

Dim dat As Date

dat = Me!DateFieldName
Me!DateFieldName = DateAdd("d", -1, DateSerial(Year(dat), Month(dat) +
1, 1))

replace DateFieldName with the correct name of the date field, of course.

hth

 

[fredg]

I am trying to create an expression in the after update property of a date
field that would enter the end of the month of the date entered in the field.
example, I enter 3/5/09, I need the update field to result in 3/31/09. Need
help

Code the Date control's AfterUpdate event:

Me.[DateField] = DateSerial(Year([DateField]),Month([DateField])+1,0)

 

[John W. Vinson]

I am trying to create an expression in the after update property of a date
field that would enter the end of the month of the date entered in the field.
example, I enter 3/5/09, I need the update field to result in 3/31/09. Need
help

The DateSerial function will do this:

Private Sub txtDate_AfterUpdate()
If IsDate(Me!txtDate) Then
Me!txtDate = DateSerial(Year(Me!txtDate), Month(Me!txtDate) + 1, 0)
End If
End Sub

The dateserial function takes a year, month number and day, and is clever
enough to interpret the zeroth day of next month as the last day of this
month.

 

[tina]

nice, John (and fredg) - much simpler than mine!

 

[John W. Vinson]

nice, John (and fredg) - much simpler than mine!

I've played around a bit with DateSerial and it's impressive:

?dateserial(2009,1,101)
4/11/2009
?dateserial(1900,1312,11)
4/11/2009

And it can handle both four and two digit years, using the 1930 cutoff (or
whatever you have it set to):

?dateserial(2009,4,11)
4/11/2009
?dateserial(9,4,11)
4/11/2009
?dateserial(99,4,11)
4/11/1999


Note that the arguments are integers so you can't use a Day value over 32767:
?dateserial(1900,1,32767) works and gives
9/17/1989
but
?dateserial(1900,1,32768)
gives an Overflow error.

And of course you can use any expression that returns an integer for any of
the arguments.

 

[tina]

wow, none of that occurred to me (i should go out and play more! <g>). this
opens up some interesting possibilities, makes for a more flexible tool in
the toolbox...i like it!