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

  • Thread starter Thread starter FatMan
  • Start date Start date
F

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
 
You cannot use = to check for Null

Change

If rs!GrowerEmail = Null Then

to

If IsNull(rs!GrowerEmail) Then
 
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
 
Back
Top