Help with code --> Invlaid use of Null????
in [Forms]
|
From: FatMan on 17 May 2010 09:46 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 17 May 2010 09:54 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 17 May 2010 10:24 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
|
Pages: 1 Prev: designing form for inserting records into separate table Next: RunTime Error 3070 |