Reporting "N/A" whenever a numeric control field is null (blank) 3

[uscitizen]

is there some way to do this?

 

[CajunCenturion]

Null and Blank are actually two different things, but you should be able to handle both with the following:

=IIf((Len(Trim(Nz([<FieldName>],&quot;&quot;))=0)),&quot;N/A&quot;,[<FieldName>])

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

we're off to a great start, but where do you put this?

 

[CajunCenturion]

That depends on exactly how you're using it. This can, as is, be used as the ControlSource property for the numeric control.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

okay, the data are being entered into a table, and the table is being used to create a query and the query's the source for the report. i've tried entering the following where the field (which is the one of interest was) in the query:


Expr1:IIf((Len(Trim(Nz([TotalProtAccrual],"&quot)=0)),"N/A",[TotalProtAccrual])

in the query design builder where the affected field, TotalProtAccrual, was beforehand after some thought on the subject.

the resulting query generates a column with #ERROR in most of the rows, the exceptions being the ones with "N/A". whenever #ERROR appears it seems to replace a blank and whenever "N/A" appears it seems to replace a number?

 

[uscitizen]

hi!

i'm ing because this simple code seems to be the solution:

Expr1: Nz([TotalProtAccrual],"N/A&quot

when i report on Expr1 in the report, N/A is published whenever the count would be otherwise empty space. Zero, 0, appears as a zero.

thanks.

 

[CajunCenturion]

The code that you have will handle the case where TotalProtAccrual is Null, but it will not handle the case where TotalProtAccrual is Blank. I think the problem with the statement I original offered is that is has one of the close parens in the wrong place.

=IIf((Len(Trim(Nz([<FieldName>],&quot;&quot))=0),&quot;N/A&quot;,[<FieldName>])

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[uscitizen]

i placed my code in a column of the query's design view (actually in two columns, since i re-use it for two numeric fields). you're saying that blank and null are not technically 'the same thing' to ms a2k. am i correct. and that your code traps those instances when either one or the other is present and re-codes the field to read 'N/A'?

 

[dhookom]

Actually you are all working too hard on this. Set the format property of the text box to:

#,##0.00;($#,##0.00);0;&quot;N/A&quot;

Check Help on &quot;Format Property Numeric&quot; for additional information.

Duane
MS Access MVP

 

[CajunCenturion]

Yes, a Null is an Empty string, and Blank is a zero-length string, and yes, the code that I presented should take care of either, and well as a string that only contains blanks, and should display an N/A in its place.

Internally, each variable has associated with it a memory address of where the value of the variable can be found. A string is a sequence of characters in when the end of the string is a byte which has a value of chr(0). If the variable has a valid memory address, but the value at the memory address is chr(0), then you have a zero-length string, or a null string - but it's still a valid string. If it doesn't have a valid memory address, then you have a null.

  Symbol Table                     Physical Memory
Var        Address                 Address    Value
--------   -------                 -------    -----
LastName       0                      45      \0
FirstName     45                      88      Programmer\0
Title         88

From the above table, LastName is by definition NULL because it does not have a valid memory address. FirstName is a zero-length string because the first byte value is the string terminator [chr(0)]. Title is a string with the value &quot;Programmer&quot;

Breaking it down the IIf statement, here is what is happening, from the inside out:
=IIf((Len(Trim(Nz(<FieldName>,&quot;&quot))=0),&quot;N/A&quot;,<FieldName>)
That piece checks to see if the field is null, and if it is, returns a zero-length string in its place, otherwise it returns the value of the field.
Next, we trim that value to remove all leading and trailing spaces:
=IIf((Len(Trim(Nz(<FieldName>,&quot;&quot))=0),&quot;N/A&quot;,<FieldName>)
Finally we check the length of that resultant string, checking it against zero, which completes the conditional phase of the IIf statement.
=IIf((Len(Trim(Nz(<FieldName>,&quot;&quot))=0),&quot;N/A&quot;,<FieldName>)
At this point, we turned the field into a zero-length string if it's null, and removed leading and trailing spaces and check to see if we still have an zero-length string. If so, our conditional is true, so we return the True portion of the IIf statement - &quot;N/A&quot; - and if the length is not zero, then we have something in the field, so our conditional is false and we return the field's value.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PortlandSam]

This post sounds like it may be the solution I am looking for, for my report, but I don't know how to use CajunCenturion's code;
=IIf((Len(Trim(Nz(<FieldName>,&quot;&quot))=0),&quot;N/A&quot;,<FieldName>)

When my current control source is allready this;
=Forms![Hourly Non]![STD GRP ID]

Any syntaxers out there?

 

[dhookom]

PortlandSam
Not sure what your question is however a wag:
=IIf(Len([Forms]![Hourly Non]![STD GRP ID] & &quot;&quot=0,&quot;N/A&quot;,[Forms]![Hourly Non]![STD GRP ID])

Or
=Nz([Forms]![Hourly Non]![STD GRP ID],&quot;N/A&quot


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PortlandSam]

That worked perfectly, thank you! And now that I have that part working, I have found out that was not the source of my problem. I have a report using a query that draws from a form that has narrowed down my tables to a single record, and my desire is to export my report of that record into Word .rtf, and it works perfectly when all fields have returned a result, but when there are resultless fields, it converts the formatted Field titles over fine to Word, but all of the results are gone, even the ones that did exist on the form in access!

So I thought if I could get it to return a result in those blank fields, that my report would convert over all the results, including the N/A's, but that doesn't seem to have fixed it, though results are now being returned into those empty fields in access. Yikes!
PortlandSam

 

[dhookom]

PortlandSam,
I think you already have a thread started regarding this issue so I would suggest that further discussions regarding this is best kept to the one, new thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.