Dtermine the Period

[Guest]

Hi,
I have 1 Table "Periods" with 3 fields:
From date - To date - Period
01/01/2004- 02/02/2004 - 01
03/02/2004- 02/03/2004 - 02 ..........
I have 13 periods.

In my Form I have a field "Date" and a field "Period"
I would like when user type the "Date", it giving automatically the exact
period from the Table.
I did it with this formula but doesn't work perfectly, is the a simply way
to do it ?

(If Me![Date] >= "2004/04/01" And Me![Date] <= "2004/05/01" Then
Me![Periode] = 1
End If )

Thanks

 

[Guest]

Here's a function that will calculate the period for you. Simply call it from
anywhere and supply the date.



Option Compare Database
Option Explicit

Public Function intPeriod(dtmCurrent As Date) As Integer



On Error GoTo HandleErr
Const cstrProcName As String = "stdCalcPeriod - intPeriod"

Dim intJDay As Integer
Dim dblPeriod As Double
Dim intPer As Integer
Dim Jan31WkDay As Integer
Dim intYear As Integer
Dim Jan31TheYear As Date
Dim intPerStart As Integer

intJDay = DatePart("Y", dtmCurrent)
intYear = DatePart("YYYY", dtmCurrent)
Jan31TheYear = DateSerial(intYear, 1, 31)
Jan31WkDay = DatePart("W", Jan31TheYear)
Select Case Jan31WkDay
Case 1
intPerStart = 31
Case 2
intPerStart = 30
Case 3
intPerStart = 29
Case 4
intPerStart = 28
Case 5
intPerStart = 27
Case 6
intPerStart = 26
Case 7
intPerStart = 32
End Select
dblPeriod = ((((intPerStart - 28) * -1) + intJDay) / 28) + 1
intPer = Fix(dblPeriod)
Select Case intPer
Case Is < 1
intPeriod = 13
Case Is > 13
intPeriod = 13
Case Else
intPeriod = intPer
End Select

ExitHere:
Exit Function

HandleErr:
intPeriod = 0
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
cstrProcName
End Select
Resume ExitHere

End Function

 

[Tim Ferguson]

Hi,
I have 1 Table "Periods" with 3 fields:
From date - To date - Period
01/01/2004- 02/02/2004 - 01
03/02/2004- 02/03/2004 - 02 ..........
I have 13 periods.

'
' Create the criterion carefully: note proper formats
' By the way, using names like "Date" is a Really Bad Idea
' So is using table names and field names with spaces in them
'
strWhere = _
"FromDate <= " & format(Me!DateControl,"\#yyyy\-mm\-dd\#") & _
" AND ToDate >= " & format(Me!DateControl,"\#yyyy\-mm\-dd\#")

'
' check it looks okay; remove this line once it's debugged
'
msgbox strWhere

'
' look up result
'
wPeriod = DLookup("Period", "Schedules", strWhere)


There is probably a neater way using Max(), but this is easy to maintain
and to understand.

Hope it helps


Tim F