Field with no Value

[Former Member]

I use CR 2008 and make an Report on Navision5

In the Table I select a field (Varchar10 -notnull) the value is '' means empty.

On SQL Server i can make selections like ...where field=''

In CR I cant edit/change(backgr.color) if it is empty - if there is a value it works.

How can I solve this. SP1 and SP2 installed

[former_member260594 · Accepted solution]

Tony,

Not being able to change the background color of a field usually only occurs when the value isnull. Try creating a formula to check if the value is coming in as a null;

isnull( )

If it comes back as true then you might want to enable the Report Option Convert Database Null values to default. This will convert a null string to ''

[Former Member · Accepted solution]

I make a select in SQL-Server on Field IsNull - no record returned - with field='' all expected values return.

If I edit the field in CR:

IF IsNull() THEN 'test' ELSE 'test1' the field still empty the field with a value print test1 (how I expected).

[former_member260594 · Accepted solution]

Tony,

My mistake, formatting us unavailable for null or empty strings. You can however workaround this by placing a formula object in the same position as the database field and suppress it when it is <> ''. You can then add the same formatting to the formula object as you have for the db field.

[Former Member · Accepted solution]

Please don't apologise. I'm thankful for any help.

If I can use a formular, how should the formular look?

To understand my Problem:

-

---

Here my steps in SQL-Server(2005):

CREATE TABLE [atest](

[Counter] [int] NOT NULL,

[textfield] [varchar](10) NOT NULL,

)

-

---

INSERT INTO [dbo].[atest]

([Counter]

,[textfield])

VALUES

(1,'')

-

---

INSERT INTO [dbo].[atest]

([Counter]

,[textfield])

VALUES

(2,'text')

-

---

select * from test where textfield=''

returns 1

-

---

Then the steps in CR2008:

Insert the fields in Detail-Section

right klick on the field --> format field...

'X+2' Button on Show string

Formular: If {atest.textfield}='' THEN 'not found' Else {atest.textfield}

result:

1

2 text

change Formular:

If isnull({atest.textfield}) THEN 'not found' Else {atest.textfield}

the same result??

In Reportoptions I set the first two checkboxes:

Convert Null Values to standard

Convert other Null Vales to standard

[Former Member]

Tony,

Why can't you say "where not equal value" ? (sorry, some symbols don't show up)

That way you don't care if it's null or not.

[Former Member]

If {atest.textfield}<<>>"'abcd' THEN {atest.textfield} ELSE 'not found'

-

---

If {atest.textfield}='abcd' THEN 'not found' ELSE {atest.textfield}

Every try to compare this field fails - only if there is an value in this field ... the field works like I expected.

Edited by: Tony Becker on Sep 9, 2008 4:11 PM

[Former Member]

Can you please try this formula :

If {atest.textfield} = ' " '

Then "This is Null"

Else "This is Not Null"

Then you can apply the colour based on the above text value.

Regards,

Sastry

[Former Member]

Sorry, sorry, sorry.

I should read, and make how Graham Cunningham say:

1. Make a formular field formular1

2. Formular is: If {atest.textfield}='' THEN 'not found' Else {atest.textfield}

3. Insert THIS!!! field(formular1) in the report

Now it works.

Thank you!!

Edited by: Tony Becker on Sep 10, 2008 11:33 AM

Edited by: Tony Becker on Sep 10, 2008 11:35 AM

[Former Member]

What?

Can you please give us more information?

Steps you take, what occurs, etc....