Creating date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where the user selects a month and year.

What I need is a second "date field" formatted as YYYY-MM-DD.

Here's the twist:
the date needs to be the end of the following month. (i.e. if the month 04
and year 2005 is selected , the date field needs to be 2005-05-31.

There are a couple of ways I started to work toward this goal. One was to
have [year]+"-"+([month]+1)+"-"+[something]. I'm not sure how to set up
something so that its the last day of the following month.

The other way would be to have it take [year]-[month+2]-1 and subtract one
day to get the last day of the following month. I'm not sure how to have the
program do this calculation though.

Thank you in advance,
 
x=dateadd("m",2,date) 'Add two months to date
x=dateserial(year(x),month(x),1) 'Changes the day to the first day of the
month
x=dateadd("d",-1,x) 'Subtracts one day

or if you want to do it in one line :

x=dateadd("d",-1,dateserial(year(date),month(date)+2,1))
 
Hi, Keith.

Although you can certainly nest calls, I think it's clearer if you use a
custom function. The code assumes the name of first control is txtFirstDate,
and the second control's name is txtEONextMonth. The AfterUpdate event of
the first sets the DefaultValue property of the second, allowing you to
override the default if you wish.

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 txtFirstDate_AfterUpdate()
txtEONextMonth.DefaultValue = "#" & dteNextMonth([txtFirstDate]) & "#"
End Sub

Hope that helps.
Sprinks
 
I have a form where the user selects a month and year.
What I need is a second "date field" formatted as YYYY-MM-DD.
Here's the twist:
the date needs to be the end of the following month. (i.e. if the month 04
and year 2005 is selected , the date field needs to be 2005-05-31.
Click to expand...

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
 
The 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.

If you need more info - I'll be happy to provide. I tried the code that
Sprinks provided, however I'm not sure if I'm passing the correct fields to
the function & sub. Here's the code I used for the NextMonth default value:

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,
 
Try something like this:

Private Sub CurrentYear_AfterUpdate()
If Not IsNull(Me.CurrentMonth) and Not IsNull(Me.CurrentYear) Then
Me.NextMonth = DateSerial(Me.CurrentYear,Me.CurrentMonth+2,0)
End If
End Sub




Keith Meier said:
The 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.

If you need more info - I'll be happy to provide. I tried the code that
Sprinks provided, however I'm not sure if I'm passing the correct fields to
the function & sub. Here's the code I used for the NextMonth default value:

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,
Rob Schneider said:
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
Click to expand...
Click to expand...
 
Back
Top