calculating a date

[gbanks]

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

 

[The_Doc_Man]

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)

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

 

[vangogh228]

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!

 

[gbanks]

Thanks worked great!!!