Problem with VB Code

Sorry if repost(IT logged me out)

Hi me again.....

I have a table full of term dates. What I need to return is the correct school year start, for the entered date. Then work out what week it is, in the academic calendar.

I have the following table:

(XML Export From Access(Other methods not work on version I had))

?xml version="1.0" encoding="UTF-8"?
xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata"
xsd:element name="dataroot"
xsd:complexType
xsd:sequence
xsd:element ref="TermDates" minOccurs="0" maxOccurs="unbounded"/
/xsd:sequence
xsd:attribute name="generated" type="xsd:dateTime"/
/xsd:complexType
/xsd:element
xsd:element name="TermDates"
xsd:annotation
xsd:appinfo
od:index index-name="PrimaryKey" index-key="School_Year " primary="yes" unique="yes" clustered="no"/
/xsd:appinfo
/xsd:annotation
xsd:complexType
xsd:sequence
xsd:element name="School_Year" minOccurs="1" od:jetType="text" od:sqlSType="nvarchar" od:nonNullable="yes"
xsd:simpleType
xsd:restriction base="xsd:string"
xsd:maxLength value="9"/
/xsd:restriction
/xsd:simpleType
/xsd:element
xsd:element name="Autumn_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Autumn_S_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Winter_Break_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Winter_S_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Winter_Break_end" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Winter_E_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Autumn_End" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Autumn_E_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Spring_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Spring_S_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Spring_Half_Term_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="SPring_HS_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Spring_Half_Term_End" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Spring_HE_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Spring_End" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Spring_E_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Summer_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Summer_S_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Summer_Half_Start" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Summer_HS_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Summer_Half_End" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Summer_HE_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
xsd:element name="Summer_End" minOccurs="1" od:jetType="datetime" od:sqlSType="datetime" od:nonNullable="yes" type="xsd:dateTime"/
xsd:element name="Summer_E_SOW" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/
/xsd:sequence
/xsd:complexType
/xsd:element
/xsd:schema

I have a form with the following function:

Private Sub setWeekNo()

Dim weekNo
Dim weekBegin

'get Entered Date
weekBegin = Week_Commencing.Value

'Find school year
Dim entYr
Dim tempSchYr
Dim startDate
Dim sqlState


  'Get year from entered date
  entYr = Right(weekBegin, 4)
  tempSchYr = entYr & "/" & (entYr + 1)
  sqlState = "[School_Year] = " & tempSchYr
  startDate = DLookup("[Autumn_S_SOW]", "TermDates", sqlState)
  'Debug message
  MsgBox ("Start Date returned:" & startDate)
  'Check if term dates entered 
  If (startDate  Null) Then
    If (DateDiff("ww", startDate, weekBegin)  0) Then
    'Not Current Yr
      tempSchYr = (entYr - 1) & "/" & (entYr)
      sqlState = "[School_Year] = " & tempSchYr
      startDate = DLookup("[Autumn_S_SOW]", "TermDates", sqlState)
      'Check if the dates entered
      If (startDate  Null) Then
        'Do Nothing
      Else
        'School Dates not entered in DB
        MsgBox ("Error, Term dates not entered!!!!")
        End
      End If
    Else
      'Do Nothing
    End If
  Else
    'Might be end of yr
    tempSchYr = (entYr - 1) & "/" & (entYr)
    sqlState = "[School_Year] = " & tempSchYr
    startDate = DLookup("[Autumn_S_WC]", "TermDates", sqlState)
    MsgBox (startDate)
    If (startDate  Null) Then
      'Do Nothing
    Else
      'School Dates not entered in DB
      MsgBox ("Error, Term dates not entered!!!!")
      End
    End If
    
  End If

weekNo = DateDiff("ww", startDate, weekBegin) + 1

MsgBox ("Week Number :" & weekNo)

'Update WeekNumber
Week_Number.Locked = False
Week_Number.Value = weekNo
Week_Number.Locked = True
  
End Sub

Thanks in advance for any help

If you wish to reply by e-mail send it to :

This Websites name at sign mr8ball dot co dot uk