Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Null values in Select Expert Post Reply Post New Topic
Author Message
tomwilson64
Newbie
Newbie
Avatar

Joined: 13 Dec 2011
Location: United States
Online Status: Offline
Posts: 2
Quote tomwilson64 Replybullet Topic: Null values in Select Expert
     Posted: 13 Dec 2011 at 5:18am
I'm using Crystal Reports 8.5 and also have Crystal Reports 2011, but am using 8.5 in this case.  This issue always perplexes me.  I have about 6717 records which I'm reporting on.  One field in all these records is sometimes null when it should have a value.  I can't make it required, so I'd like to create a report which allows me to list only the entries where no value was entered, that is to say a null value.  In SelectExport I can select the field (it's named Files.Custom13 and is a text field) and I have created formulas before so I'd like to create a formula which allows the Select Expert to produce only results where the field's value is null.  I have one other select expert condition so the statement to return only the records where Custom13 is null will be after the operator "and" Here's what the part before "and" states:
 
{PREFIX.Prefix_Name} = "REG.SWR" and
 
How do I complete the formula so slect export knows to return only those values (within the REG.SWR prefix) which have a null value in Files.Custom13 ?
 
Many thanks for your assistance.
 
 
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 13 Dec 2011 at 6:03am
try
 
using formula
{PREFIX.Prefix_Name} = "REG.SWR" and
len({Files.Custom13})<1


Edited by comatt1 - 13 Dec 2011 at 6:03am
IP IP Logged
tomwilson64
Newbie
Newbie
Avatar

Joined: 13 Dec 2011
Location: United States
Online Status: Offline
Posts: 2
Quote tomwilson64 Replybullet Posted: 13 Dec 2011 at 8:03am

Comatt1,

I tried your formula, but it returned no records.  I've tried the same thing you suggest a few ways, but Crystal doesn't count null values as having a value so it doesn't return anything as less than one because the records where Custom13 is null, it doesn't have any value returned so it doesn't compute.
 
Other ideas?  Anyone?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Dec 2011 at 8:15am
isnull({Files.Custom13}) and {PREFIX.Prefix_Name} = "REG.SWR"
 
You must use the ISNULL condition first
 
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 13 Dec 2011 at 8:37am
make sure your CR options, dont convert nulls to default values, in your main report settings
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum