Date field revisited

[Guest]

Sorry for reposting this, just wanted to see what I might be coding
incorrectly:

A user selects the month in a "Month" combo box and types in the year in a
"Year" text box.

What I would like to have happen is when both fields are completed, the
NextMonth field gets filled in automatically.

Currently I have the following subs coded:

Private Function dteNextMonth(dteFDate As Date) As Date
'find first day of the month
dteNextMonth = DateValue(DatePart("m", dteFDate) & "/1/" & DatePart("yyyy",
dteFDate))

'add two months
dteNextMonth = DateAdd("m", 2, dteNextMonth)

'Subtract one day
dteNextMonth = DateAdd("d", -1, dteNextMonth)
End Function

Private Sub NextMonth_AfterUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub

Private Sub NextMonth_BeforeUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub


Thanks again for your help so far,

 

[Steve Schapel]

Keith,

If I understand you correctly, you want a date entered which is the 1st
of the following month, right? How about this...

Me.NextMonth = DateAdd("m",1,DateSerial(Me.txtYear,Me.cboMonth,1))

A couple of comments... 'Month' and 'Year' are Reserved Words (i.e.
have a special meaning) in Access, and as such should not be used as the
names of fields or controls. Secondly, your example code appears to be
using the BeforeUpdate and AfterUpdate events of a control to set the
Default Value of that control. I can't figure out what you are trying
to achieve with this, but I suspect this is not what you really need.

--
Steve Schapel, Microsoft Access MVP

Sorry for reposting this, just wanted to see what I might be coding
incorrectly:

A user selects the month in a "Month" combo box and types in the year in a
"Year" text box.

What I would like to have happen is when both fields are completed, the
NextMonth field gets filled in automatically.

Currently I have the following subs coded:

Private Function dteNextMonth(dteFDate As Date) As Date
'find first day of the month
dteNextMonth = DateValue(DatePart("m", dteFDate) & "/1/" & DatePart("yyyy",
dteFDate))

'add two months
dteNextMonth = DateAdd("m", 2, dteNextMonth)

'Subtract one day
dteNextMonth = DateAdd("d", -1, dteNextMonth)
End Function

Private Sub NextMonth_AfterUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub

Private Sub NextMonth_BeforeUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub


Thanks again for your help so far,
:

Without knowing how the the user is specifying the month and year it is
impossible to give you a complete answer, but use of the DateSerial function
with the Day set to zero should handle this nicely.

=DateSerial([YearValue],[MonthValue] +2 ,0)
This actually specifies the 0th day of the month after next, which to the
computer is the same thing as the last day of next month.

Rob

 

[Guest]

I was hoping to get the end of the following month (i.e. user selects 04 in
month combo box and enters 2005 in the year text box and the NextMonth box
would show 2005-05-31)

Didn't know about year and month being reserved words - thank you, I'll
adjust that part.

I will need to use the YYYY-MM-DD date format for a query and was hoping to
have that autocalculated as the NextMonth field.

Keith,

If I understand you correctly, you want a date entered which is the 1st
of the following month, right? How about this...

Me.NextMonth = DateAdd("m",1,DateSerial(Me.txtYear,Me.cboMonth,1))

A couple of comments... 'Month' and 'Year' are Reserved Words (i.e.
have a special meaning) in Access, and as such should not be used as the
names of fields or controls. Secondly, your example code appears to be
using the BeforeUpdate and AfterUpdate events of a control to set the
Default Value of that control. I can't figure out what you are trying
to achieve with this, but I suspect this is not what you really need.

--
Steve Schapel, Microsoft Access MVP

Sorry for reposting this, just wanted to see what I might be coding
incorrectly:

A user selects the month in a "Month" combo box and types in the year in a
"Year" text box.

What I would like to have happen is when both fields are completed, the
NextMonth field gets filled in automatically.

Currently I have the following subs coded:

Private Function dteNextMonth(dteFDate As Date) As Date
'find first day of the month
dteNextMonth = DateValue(DatePart("m", dteFDate) & "/1/" & DatePart("yyyy",
dteFDate))

'add two months
dteNextMonth = DateAdd("m", 2, dteNextMonth)

'Subtract one day
dteNextMonth = DateAdd("d", -1, dteNextMonth)
End Function

Private Sub NextMonth_AfterUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub

Private Sub NextMonth_BeforeUpdate()
NextMonth.DefaultValue = "#" & dteNextMonth([Month] & "/1/" & [Year]) & "#"
End Sub


Thanks again for your help so far,
:

Without knowing how the the user is specifying the month and year it is
impossible to give you a complete answer, but use of the DateSerial function
with the Day set to zero should handle this nicely.

=DateSerial([YearValue],[MonthValue] +2 ,0)
This actually specifies the 0th day of the month after next, which to the
computer is the same thing as the last day of next month.

Rob

 

[Steve Schapel]

Keith,

Is NextMonth bound to a field with a Date/Time data type, or bound to a
field with another data type, or an unbound control? Doing it like this
will give you the required date...
Me.NextMonth = DateAdd("m",2,DateSerial(Me.txtYear,Me.cboMonth,0))

If it's a date, then the format only affects the way it is displayed,
not the value, so there's no need to "autocalculate" in this aspect.

 

[Guest]

The month, year and next date fields are unbound controls.

I'll see if I can use this formula.