IsNull() or Me.Doc = 0

[Joseph]

I am having a problem with this code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If IsNull(Me.CadetID) Or Me.CadetID = Null Then
Me.cmbCadetID.Visible = False
Me.lblCadetID.Visible = False
Me.cmbCadetSig.Visible = False
Me.lnCadetSig.Visible = False
Else
Me.cmbCadetID.Visible = True
Me.lblCadetID.Visible = True
Me.cmbCadetSig.Visible = True
Me.lnCadetSig.Visible = True
End If

If IsNull(Me.Doc) Or Me.Doc = 0 Then
Me.lnFacNurse.Visible = False
Me.FacNurse.Visible = False
Me.FacNursePosit.Visible = False
Else
Me.lnFacNurse.Visible = True
Me.FacNurse.Visible = True
Me.FacNursePosit.Visible = True
End If

End Sub

The error code:

Run-time Error :2427
You entered an expression that has no value.

The Data:
CREATE DATABASE IF NOT EXISTS Incidents;
USE Incidents;

DROP TABLE IF EXISTS `FacilityIncidents`;
CREATE TABLE `FacilityIncidents` (
`FacIncidId` int(10) NOT NULL auto_increment,
`StaffID` int(10) NOT NULL,
`CadetID` int(10) default NULL,
`DTG` datetime default NULL,
`Location` varchar(100) default NULL,
`Subject` varchar(255) default NULL,
`Description` longtext character set utf8,
`User` varchar(50) NOT NULL,
`Computer` varchar(50) NOT NULL,
`TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`IncidTypeID` int(10) unsigned NOT NULL,
`Doc` tinyint(4) default '0',
PRIMARY KEY (`FacIncidId`),
KEY `FacIncdId` (`FacIncidId`),
KEY `StaffId` (`StaffID`),
KEY `StaffId1` (`CadetID`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO `FacilityIncidents`
(`FacIncidId`,`StaffID`,`CadetID`,`DTG`,`Location`,`Subject`,`Description`,`User`,`Computer`,`TimeStamp`,`IncidTypeID`,`Doc`) VALUES
(1,1,NULL,'2007-06-28 00:00:00','Control
Tower',NULL,’test’.','','','2009-10-23 16:40:46',0,0),
(2,1,NULL,'2007-06-26 00:00:00','Control
Tower',NULL,’test’.','','','2009-10-23 16:40:46',0,0),
(3,1,NULL,'2007-08-04 16:00:00','Control
Tower',NULL,’test’.','','','2009-10-23 16:40:46',0,0),
(4,1,NULL,'2007-07-25 00:00:00','Control
Tower',NULL,’test','','','2009-10-23 16:40:46',0,0),
(5,1,NULL,'2007-09-27 00:00:00','Boot Camp Facility
Gate',NULL,’test.','','','2009-10-23 16:40:46',0,0),
(11,1,11,'2008-01-01
12:00:00','asdfasd','test','test','martinezjr','TRAININGNCO','2009-10-23
16:40:46',0,0),
(12,1,11,NULL,'TEST','TEST','TEST','MARTINEZJR','PLTSGT','2009-10-23
16:42:55',0,0),
(13,1,NULL,'2009-01-01
12:00:00','Test','asdfasdf','Testasdfasdfasdfasdfasdfasdf','drillinstructor','DRILL-INSTRUCTO','2009-10-23 16:40:46',0,0),
(14,1,58,'2009-01-01
12:12:00','Test','test','Testtesetsets','drillinstructor','DRILL-INSTRUCTO','2009-10-23 16:40:46',0,0),
(15,1,NULL,'2009-10-10
16:00:00','TEST','TEST','TEST','drillinstructor','DRILL-INSTRUCTO','2009-10-23 16:43:05',0,0);


The first part dealing with Me.CadetID works fine, but with the second part,
dealing with Me.Doc, that error keeps showing up. I have tried different ways
to that same statement using Nz(), Select Cases, !IsNull(), etc, but always
the same answer.

Please Help!!

 

[Jeff Boyce]

Joseph

What happens if you add a breakpoint into your code to inspect the values as
it runs?

You might want to take a look at the Nz(), you might be able to simplify
your "If..." testing to something like:

If Nz(Me!CadetID,"")="" Then

Notice that the test and comparison is to a zero-length string ... to
humans, a null, a zls, and spaces all look like a "blank".

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

You are using the dot (".") after "Me". The dot, I believe, refers to
Access' 'internals' (e.g., collections, etc.).

Since I'm guessing you created a control named "CadetID", perhaps you need
to use the bang ("!") between Me and the user-created object (e.g.,
"CadetID").

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jinjer]

Hi, Joseph.

Try IsNull(Me.CadetID) = True

 

[Joseph]

Thank you for your help. I have tried all those and retried them.

But, I was able to find to problem. It was in the Record Source. I had
made a query to join information from other tables. And my joins where set to
"Only when fields from both tables are equal", reducing the record count from
11 to 3. So when the report was asking for the data, that was not one of the
three, and going thru the code, there was no data to check, making it error
out.

Again, thank you.

Joseph

 

[Steve Sanford]

If IsNull(Me.CadetID) Or Me.CadetID = Null Then

Also, note that the following expression will *always* evaluate to FALSE:

Me.CadedID = Null


Nothing is equal to NULL, not even NULL.
If you write "IF NULL = NULL" the result will be FALSE, not TRUE. While it
doesn't hurt anything, I would remove it.

HTH