From: FatMan on
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

From: Douglas J. Steele on
You cannot use = to check for Null

Change

If rs!GrowerEmail = Null Then

to

If IsNull(rs!GrowerEmail) Then


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"FatMan" <FatMan(a)discussions.microsoft.com> wrote in message
news:81B9DB79-2AA4-455B-96BD-DD05FEC941AC(a)microsoft.com...
> 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
>


From: John Smith on
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

FatMan wrote:
> 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