Problem with VB Code

[masterkeys]

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


Code:
--------------------

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsd="urn:schemas-microsoft-comfficedata">
<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