Report ignoring format of text boxes & query fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why do some text boxes on my report ignore number formatting? In these cases,
the normal format options are not available, and even manual entry of formats
is ignored. It appears that it has something to do with the underlying
queries, because the same thing is true of several of the underlying queries
(the report's RecordSource is a query based upon another query that, in turn,
is based upon a union query.

Is the only option to manually Round in the text boxes on the report?
 
If your "numbers" are from a calculation such as IIf(...) or Format(...),
they are probably be treated like text fields. You may need to wrap
calculations in Val(...) to get them to be numeric.

You can tell which values will be interpreted as numbers or text by viewing
the datasheet and seeing if they are left or right aligned.
 
Thanks, Duane. That was it. Buried deep within the query stack was this:

Sum(nz(FieldName,0))

which I changed to this:

Val(Sum(nz(FieldName,0))

I did not realize that Nz returned a Variant, and I assume this is the
source of my problem.

You might know an easier way to do what I am doing, without using Nz. The
above field is in a query where I am using an unequal Join but need to ensure
that I get non-null value for FieldName for the records where the joined
field is null (to avoid null values in later calculations); thus the Nz.
Would it be better to do a union query like this:

1. SELECT (includes simple Sum of the matching records)
UNION
2. SELECT(includes an explicit zero for the nonmatching records)

or do I just need to make sure to use Val() when I need to return a non-null
value?
 
Usually this means that the textbox contains a text string vice a number.

Number fields can be converted to strings if you use
--the format function on the field in the query
--NZ in the on the field in the query
--Concatenate two fields into one (and one is a text field)
--IIF statement that assigns a string in one part of results - IIF(A=0,"",A)
--Union query with non-matching items - query A has a fieldA, queryB has no
fieldA so you substitute ""
-- and probably others that don't come to mind right now.
 
Whoops, forgot to mention possible solutions.

In the control force the type, the control name must be different than the
field's name. Do something like:

Control Source: =Val(SomeFieldName)

Change the queries to make sure they return a number vice a string. For
example,
CCur(NZ(MyField,0))
 
Thank you. I had pretty much isolated everything else, but after reading
Duane's post, I discovered Nz as the culprit. I had never thought about the
effect of NZ being a Variant instead of an Integer.

Thanks to Rick Fisher's Find and Replace utility, it wasn't all that
difficult to find all the instances of NZ in the program and get them fixed
to Val(Nz....)
 
Back
Top