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

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