Help with code --> Invlaid use of Null????

[FatMan]

Hi all:
Can someone please let me know what I am doing wrong in the below
code.....it is not all the code but just part of it. What I am doing is
using a form to email copies of a report to our growers/customers. When the
code loops through our grower/customer table and the grower has an email
address it works perfect. However, when it hits a grower without an email
address it then fails and gives me an "invalid use of null" error.

I thought I was checking for "null" email addresses and the processing below
what code I have shown will handle them. The problem I am having is why will
the If statement not recognize the fact that the email is null? When the
debug.print statement prints it will show the grower/customer name and for
the portion dealing with the email address will print "**". As well when I
put a stop/break control on my code and run it in debug mode and step through
it line by line when I hover my mouse of the rs!GrowerEmail it displays
"rs!GrowerEmail = Null".

So, my question/problem is why is my If statement not recognizing that
rs!GrowerEmail is null?


Code follows......


Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmail")
rs.MoveFirst

If rs.RecordCount < 1 Then
MsgBox "Sorry no records selected. Please check the form for
errors.", vbCritical, "Error - No Records"
Exit Sub
End If





Do While Not rs.EOF
'transfer values to variables
strGrowerFarmName = rs!GrowerFarmName
intGrowerID = rs!GrowerID
strGrowerNo = rs!GrowerNo

Debug.Print "strGrowerFarmName = " & strGrowerFarmName & " " &
"strGrowerEmail = *" & rs!GrowerEmail & "*" & " " & Len(rs!GrowerEmail)

If rs!GrowerEmail = Null Then
rs!GrowerEmail = "No email address on file"
Else
strGrowerEmail = rs!GrowerEmail
End If
..
..
..other processing here......
..
..
Loop

Thanks,
FatMan

 

[Douglas J. Steele]

You cannot use = to check for Null

Change

If rs!GrowerEmail = Null Then

to

If IsNull(rs!GrowerEmail) Then

 

[John Smith]

If you remember that Null means 'I don't know the value of this' you will
understand that nothing can ever be equal (or not equal) to it. Use:

If IsNull(rs!GrowerEmail) Then

instead.

HTH
John
##################################
Don't Print - Save trees