I have a form called Date_frm. On this form are 2 fields FirstDate and EndDate. What I would like to do is have the user enter into the Firstdate field the first day of the month ie:10/01/02. Them I would like for them to press a button and have the EndDate field calculate what the last day of the date entered into the FirstDate field is. Ie: 10/31/02. Does any know how to do this? Thanks..
I'll summarize how this could be done. Since you have a form, that means you could do this with VBA code in the form's class module. This would have been trickier as a query because of some severe nesting that would have to happen.
Assume that the user's input on the form is a control named [InputDate]. Use the form's event code for InputDate_LostFocus (one of many possible choices, see later discussion after the code)
Code:
Private Sub InputDate_LostFocus()
Dim strDate1 as String
Dim dtmDate1 as Date
Dim dtmDate2 as Date
dtmDate1 = CDate( [InputDate] )
strDate1 = Format( "dd mmm yyyy", strDate1 )
Mid$(strDate1, 1, 2 ) = "01"
' Guarantee that strDate1 represents the first day of the month
dtmDate1 = CDate( strDate1)
dtmDate2 = DateAdd( "m", 1, dtmDate1 ) '1st of next month
dtmDate2 = DateAdd( "d", -1, dtmDate2 ) 'step back one day
' now date2 is for the last day of the same month as date1
' at this point, do whatever you need with the two dates. e.g.
[InputDate] = strDate1
[LastDOM] = Format( "dd mmm yyyy", dtmDate2 )
End Sub
I picked the _LostFocus event because if you chose the "_Change" event you wouldn't always have a valid date in the [InputDate] field. I.e., until your user has entered a complete date, the date field will come up with the wrong answers and will do a lot of computing for nothing. You could easily chose some other event such as _Click of that field.
I also picked an arbitrary format for the date just because I wanted the day of the month to be in a fixed location where I could extract or overwrite it on demand.
If you are not sure about the functions I used, look them up in the help files.
Mid STATEMENT (don't look up Mid FUNCTION, not the same.)
Format Function (& User-defined date/time strings )
DateAdd Function
CDate
Doc_Man: Couldn't a formula using "SerialDate" be used for this field? I have used it in the past... mostly because I don't know any VBA... well, only enough to destroy the thing!