if statement / len problem

[lydz]

im not sure what exactly is the problem here
its based on a school library system

Private Sub Combo45_Click()
Dim Year As Integer
Dim Form As String
Dim Booksout As Integer
Form = Forms![frmlend]![Child52].Form![Form]
Booksout = Forms![frmlend]![Child52].Form![Booksout]

If Len(Form) = 3 Or 6 Then
Year = Left(Form, 1)
ElseIf Len(Form) = 4 Or 7 Then
Year = Left(Form, 2)
End If

If Booksout = 2 And Year < 12 Then
MsgBox ("This student has the max number of books out")
ElseIf Booksout = 4 And Year > 11 Then
MsgBox ("This Sixth Form Student has the max amount of books out.")
End If

So the form group are in various forms e.g 7HP, 12HP, 7HP/HY, 12HP/HY well those 4 forms are what the Len is doing is extracting the year group of the student
then the year goes into the next if statement
when the the year group was 7 and [booksout] = 2 the whole thing worked
but when the year group was 13 and [booksout] was 4, no msgbox was displayed and it should have been
i put the second if statement inside the first and it still didnt work
it's probably really simple and im a bit suspicious of the Year variable if statement
wondered if anyone here could spot the problem

 

[missinglinq]

First off, you need to rename your variables; Form and Year are reserved words in all versions of Access! When in doubt check here:

http://www.databasedev.co.uk/ms-access-reserved-words.html

You can do soemthing as simple as adding an extra letter, i.e FForm and YYear.

Next your use of OR is not correct syntax.

If [B]Len(Form) = 3 Or 6 [/B]Then
Year = Left(Form, 1)
ElseIf [B]Len(Form) = 4 Or 7[/B] Then
Year = Left(Form, 2)
End If

needs to be

If [B](Len(FForm) = 3) Or (Len(FForm) = 6)[/B] Then
   YYear = Left(FForm, 1)
ElseIf [B](Len(FForm) = 4) Or (Len(FForm) = 7) [/B]Then
   YYear = Left(FForm, 2)
End If

Make these corrections and see how she runs!

 

[lydz]

thanks!
made the changes and now it works a treat!
cheers

 

[missinglinq]

Glad you got it working!