Invalid use of Null

[sambrierley]

Hi all,

im trying to build my own custom search form that will basically loop through all the records until a match is found. i dont expect the user to know every bit of information so im allowing nulls by the use of "Or IsNull(Variant) = True"

problem is i keep getting the invalid use of Nulls. ive used

msgbox IsNull(Variant)

to confirm that both TRUE and FALSE are returned.
any help would be appreciated. thanks

Dim Schedule As Variant
Dim Part As Variant
Dim Annex As Variant
Dim Appendix As Variant
Dim Part2 As Variant
Dim Appendix2 As Variant
Dim Ref_ID As Variant

Schedule = Forms!Search_Form!Search_Schedule
Part = Forms!Search_Form!Search_Part
Annex = Forms!Search_Form!Search_Annex
Appendix = Forms!Search_Form!Search_Appendix
Part2 = Forms!Search_Form!Search_Part2
Appendix2 = Forms!Search_Form!Search_Appendix2


Forms!Requirements_Form.SetFocus

For i = 1 To 4664

DoCmd.GoToRecord acDataForm, "Requirements_Form", acGoTo, i


Forms!Requirements_Form.SetFocus


MsgBox IsNull(Schedule)

If CStr(Forms!Requirements_Form.Schedule) = Schedule Or IsNull(Schedule) = True Then
    If CStr(Forms!Requirements_Form.Part) = Part Or IsNull(Part) = True Then
        If CStr(Forms!Requirements_Form.Annex) = Annex Or IsNull(Annex) = True Then
            If CStr(Forms!Requirements_Form.Appendix) = Appendix Or IsNull(Appendix) = True Then
                If CStr(Forms!Requirements_Form.Part2) = Part2 Or IsNull(Part2) = True Then
                    If CStr(Forms!Requirements_Form.Appendix2) = Appendix2 Or IsNull(Appendix2) = True Then
                        If CStr(Forms!Requirements_Form.Ref_ID) = Ref_ID Or IsNull(Ref_ID) = True Then
                        
                        MsgBox ""
                        
                        End If
                    End If
                End If
            End If
        End If
    End If
End If

 

[Ranman256]

remove the nulls by adding an empty string:

Schedule = Forms!Search_Form!Search_Schedule & ""

 

[TJPoorman]

Creating a search form the way you are will be extremely slow looping through every record.
Check this link out from Allen Browne on creating a search form.