How to add text to a null value field in an access report (1 Viewer)

[AlliCarr]

HI,

I am trying to save myself the work of creating 2 reports and 2 separate queries for the sake of one field.

On one of my forms, I have added a button to export a report of the information on the form. The report has a field 'ManagementDeliverability' which won't always be completed in the relevant form. However, I don't want the field to appear blank on the occasion when it isn't completed but I need to keep it in the report for the occasions when it is. I have been trying to add "N/A" to the field when the report is exported but I am struggling to make this work. I have tried a few different methods based on what I have done in the past and based on some of the other threads in the forum and I'm not sure where I am going wrong.

I added IIf(IsNull([Forms].[GrantsF].[ManagementDeliverability]),"N/A",[ManagementDeliverability]) in the criteria for the 'ManagementDeliverability' field in the query. This gave me a blank report.

I also tried this as a separate expression in a new query column. This gave me a completed report but the field was still blank and "N/A" wasn't added. I then left this as a separate expression, unticked the 'show' box and added <>False to the criteria and got the same result.

I have tried adding this to the control source on the report but get the #Size! error.

I was wondering whether it would be better to use VBA but I wouldn't know where to start. The code currently behind the button is:

DoCmd.OutputTo acOutputReport, "EnglandApplicationCopySub", acFormatPDF, "\Database\Reports\" & "Grant URN" & " " & Me.GrantURN & " " & Format(Date, "dd mm yyyy") & ".pdf", True

Any help would be very much appreciated.

 

[DHookom]

If I understand, don’t place the IIf() expression in the criteria. Place it in the field (first row) of the query design. I would use Nz() rather than IIf().

Nz([ManagementDeliverability],"N/A")

 

[The_Doc_Man]

A field can appear to be empty for either of TWO reasons... either (A) it contains / is a null, or (B) it contains a zero-length string. But a ZLS doesn't behave as a null, it behaves as a (very short) string. It will result in FALSE from the IsNull test. You could try to concatenate "" (an empty string) to the field if you expected it to be a string in the underlying source. In that case, the test wouldn't be

IsNull( [field] )

but instead,

IIF( LEN( [field] & "" ) = 0, <true>, <false> )

This test would be different if you expected a numeric field.

EDIT: DHookom's comments are also valid to consider.

 

[DHookom]

I like The_Doc_Man's solution also that avoids confusion between Null and zero length strings.

I also like his signature "Certified Grandpa (3 grandsons, 1 granddaughter). Retired and loving it. Must be doing something right 'cause my wife hasn't clobbered me after 30 years of marriage." I have the same count and genders of grandkids, but I am about 20 more years married and not clobbered.

 

[Ken Sheridan]

This illustrates the importance of defining each column (field) appropriately in table design view. In your case the appropriate properties would be:

Required: No
AllowZeroLength: No

The first means that the column position can be Null, the second that a string expression of zero length cannot be stored at the column position. When applying a criterion to this column, therefore, you can be sure that the IS NULL operator will correctly evaluate to TRUE if no value has been entered at the column position. It also means that you can apply the NZ function and be sure that this will return whatever value you want to be returned in place of NULL

It is important to be aware what Null actually represents. Null is not a value but the absence of a value. The nearest it has to a meaning is 'unknown'. Consequently Null cannot be compared to any value. x=NULL for instance will never evaluate to TRUE or FALSE, whatever the value (or lack of value) has been assigned to x. The result will always be NULL, even if x is NULL. This is why we have the IS NULL and NOT IS NULL operators for use in queries, and the IsNull() function for use in VBA.

Another feature of NULL is that it 'propagates' in arithmetical operations, so 1+2+NULL will not return 3, but NULL. If we want to allow for NULL in such operations, then we'd use the NZ function e.g. Nz(a)+Nz(b)+Nz(c) will return 3 if a = 1, b = 2 and c is NULL. This behaviour of Null can be used to advantage in concatenating string values, e.g. FirstName & " " & LastName would return an unwanted leading space if FirstName is NULL. The following expression on the other hand, (FirstName + " ") & LastName, will suppress the space because, by using the arithmetical + operator rather than the & (ampersand) concatenation operator, (FirstName + " ") will evaluate to NULL, which will then be concatenated with the value of LastName, as the & operator allows NULLs.

 

[The_Doc_Man]

Ken's 3rd paragraph about null propagation really comes into play when dealing with names. In my genealogy database, when researching six or more generations back in time, sometimes I have a person's middle name, sometimes not. That is when you really need to appreciate zero-length strings and how to detect and manipulate them.

 

[Isaac]

Never allowing nulls in a particular column is also something to consider - not saying you necessarily should or shouldn't for any given column, it's just an approach some people take and you can consider it for your Input process. then you don't have to be constantly wondering/worrying if something is "really null", but rather it will always have the default value if it hasn't been intelligently filled in by the business.

 

[arnelgp]

that is because your IIF() criteria is incomplete, change:

IIf(IsNull([Forms].[GrantsF].[ManagementDeliverability]),"N/A",[ManagementDeliverability])

To:

IIf(IsNull([Forms].[GrantsF].[ManagementDeliverability]),"N/A",[Forms].[GrantsF].[ManagementDeliverability])

 

[AlliCarr]

Thank you all for your responses. I have tried the easier ones but still having the problem.

When I have checked the properties of the column in the table the 'Allow Zero Length' option is set to yes so I think that will be my problem. I'll update this later today once everyone has logged out of the database. (Staff tend not to like it if I boot them out when they are in the middle of something!). I'll let you know how I get on.

And thank you @Ken Sheridan for your clear explanation of the behaviour of NULL, this will really help with future development.