If date Today is greater than 1st March, but
less than 1st September in any year, I need
to inert into an unbound control, the September
of the same year. If the date today is greater
than 1st Sepetember of current year, but
less than 1st January of the following year, I
need to insert March of the following year.
If date today is greater than 1st January, but less
than 1st March, I need to insert March of the
current year.
For my example, I'll use "OrderDate" as the date of interest in the record,
and txtHalf, as the name of the unbound control. To show the dates for 1 Mar
and 1 Sept, the following code worked for me in a Form showing Orders from
the Northwind Traders example database. I put it in the OnCurrent event:
Private Sub Form_Current()
Dim intYear As Integer
Dim datSept As Date
Dim datMar As Date
intYear = Year(Me!OrderDate)
datSept = DateSerial(intYear, 9, 1)
datMar = DateSerial(intYear, 3, 1)
If Me!OrderDate < datMar Then
Me!txtHalf = datMar
ElseIf Me!OrderDate >= datMar And Me!OrderDate < datSept Then
Me!txtHalf = datSept
Else 'Me!OrderDate >= datSept Then
Me!txtHalf = DateAdd("yyyy", 1, datMar)
End If
End Sub
If you want to use a different format for the dates, put a Format on the
txtHalf control. You will need to add any error handling and it's entirely
possible that the code could be "streamlined". I think, as it is, it
shouldn't be too difficult to understand.
Larry Linson
Microsoft Access MVP
