Access97: Unknown Error

  • Thread starter Jonathan Scott via AccessMonster.com
  • Start date
J

Jonathan Scott via AccessMonster.com

In a report, I am getting "#Error" in one of my fields, and I can find no
way to make it tell me what the error is. What can I do to make Access97
tell me what exactly it is having a problem with?

Jonathan Scott
 
D

Duane Hookom

There are a number of reasons that you might see this. Perhaps you should
provide use with some information about the report, its records source, and
the control source of the text box.
 
J

Jonathan Scott via AccessMonster.com

Here is the query:

=IIf(IsNull([newOfficeCode]) Or IsEmpty([newOfficeCode]),[officeCode],
[newOfficeCode])

When newOfficeCode is not null, I get the newOfficeCode, as expected. When
it is null/empty, I get "#Error". Both officeCode and newOfficeCode exist
in my table and in the query I use as the record source for the report.
This is inside textbox, and officeCode and newOfficeCode are of the same
data type.

Lastly, in the original version, only officeCode was used, and it had no
problem. I now want to make this report display newOfficeCode in the event
there is in fact one to display. Just can't figure what gives.

Jonathan Scott
 
D

Duane Hookom

Try either:
=Nz([newOfficeCode],[officeCode])
or
=IIf(Len([newOfficeCode] & "")=0,[officeCode], [newOfficeCode])
 
J

Jonathan Scott via AccessMonster.com

Doesn't work. I was hoping there was a way to make Access give meaningful
information. Is there no hope that I can debug it on my own without
resorting to trying every single possible permutation just to find out what
the problem is?

Jonathan Scott
 
J

Jonathan Scott via AccessMonster.com

I've tried to just simply give it officeCode, and it prints it out. When I
add the iff statement to decide whether to print out officeCode or
newOfficeCode does officeCode not work anymore.

What I need is for Access, in general, to give better error handling and
reporting. I should be able to understand the root cause of problems by the
error given. Is there no way to force it to do so? Or am I stuck with it
like this until my company upgrades? Perhaps even longer???

Jonathan Scott
 
D

Duane Hookom

If you really used "iff" then that is the issue.
Also, when provided with a hint about the name of the control, you didn't
respond to the help that was provided.

I'm not quite sure what else you would expect Access to provide in the way
of Help. If you divide by zero you will get a descriptive error. If you
provide a field name that isn't in the report record source, you will be
prompted for the field. If you type "iff" rather than "IIf", what would you
expect Access to do? When you use a function properly, you will notice some
capitalization. For instance iif will become IIf.
 
J

Jonathan Scott via AccessMonster.com

IF it were really the problem of a difference between "iff" and "IIf", then
that much would be READILY apparent. Obviously, that is not the issue.

What name of what control, and what help was offered concerning that that I
did not in fact respond to? The variable "officeCode" works on its own;
when it becomes a part of an "IIf" statement, it then refuses to work.

I've stated what I expect; that a program purported to be "Professional"
would be professional enough to properly explain what in fact it may be
having trouble with. Do you tell your doctor "I have a problem" and when
he/she asks "what is the problem?" you state once more "I have a problem"?
No, you explain it further. If Access is having a problem, it should
properly explain what the problem is.

It seems this is often one of the finer points of software engineering
missed upon in the past. I can only hope that future versions have this
problem fixed. Either way, I'm nowhere nearer to solving this problem.
 
D

Duane Hookom

I made this suggestion a few posts back:
"Make sure the name of the control is also not the name of a field."
This was like a doctor asking a patient regarding some possible causes of a
pain. You never responded either way. If you didn't understand what "the
control" meant or how to find its Name property, just ask. "the control" is
your text box that displays "#Error". Every control has a Name property.

This is a very common error when designing reports. You drag a field on from
the field list on to your report and the control name generally defaults to
the name of the field. If you then change the Control Source of the control
to an expression, you will see "#Error" in your text box/control when
previewing or printing the report.
 
Top