I have two reports with the same text box in each. The Control Source for
each is the same and is:
=Mid([Zipcode],1,5) & "-" & Mid([Zipcode],6,4)

This formula works in one report but gives "#Error" for the other.
Any ideas why?

ctdak

Is there a field or control named Zipcode in the report that doesn't work?

--

Ken Snell
<MS ACCESS MVP>

"ctdak" <(E-Mail Removed)> wrote in message
news:F8C95BBF-C107-4980-8834-(E-Mail Removed)...
> I have two reports with the same text box in each. The Control Source for
> each is the same and is:
> =Mid([Zipcode],1,5) & "-" & Mid([Zipcode],6,4)
>
> This formula works in one report but gives "#Error" for the other.
> Any ideas why?
>
> ctdak
>

ctdak wrote:

>I have two reports with the same text box in each. The Control Source for
>each is the same and is:
>=Mid([Zipcode],1,5) & "-" & Mid([Zipcode],6,4)
>
>This formula works in one report but gives "#Error" for the other.
>Any ideas why?

Make sure the text box is named something other than ZipCode
(e.g. txtZipCode).

--
Marsh
MVP [MS Access]

You were right. That was the problem! The text box was named the same as
the field name ZipCode. Strange that this should matter though. When you
add a control for a field, Access automatically makes the control name and
the control source equal the field name, so why does the control name have to
be different when you change the control source to a formula using the field
name?

ctdak


"Marshall Barton" wrote:

> ctdak wrote:
>
> >I have two reports with the same text box in each. The Control Source for
> >each is the same and is:
> >=Mid([Zipcode],1,5) & "-" & Mid([Zipcode],6,4)
> >
> >This formula works in one report but gives "#Error" for the other.
> >Any ideas why?
>
> Make sure the text box is named something other than ZipCode
> (e.g. txtZipCode).
>
> --
> Marsh
> MVP [MS Access]
>

ctdak wrote:

>You were right. That was the problem! The text box was named the same as
>the field name ZipCode. Strange that this should matter though. When you
>add a control for a field, Access automatically makes the control name and
>the control source equal the field name, so why does the control name have to
>be different when you change the control source to a formula using the field
>name?


If the control is just bound to a field, then you do not
use the = sign and Access "knows" that it should get the
field from that table/query and there is no ambiguity.

In expressions, the confusion arises because it can refer to
a field in the form's record source table/query or a control
on the form by just using its name anywhere. There is no
syntax to indicate which one you want to use. So, if you
have a control named ZipCode and it has an expression like
yours, then, Access thinks the expression's ZipCode is
referring to the control named ZipCode, which is an infinite
loop, thus the #Error.

As far as Access defaulting the control name to the field
name when you drag a field from the field list to the
form/report, you've just joined a very long list of people
with the same complaint. A lot of folks avoid this isuue by
developing the habit of prefixing their control names with
an abbreviation of the type of the control (e.g. txt, cbo,
etc) whenever they refer to a control in a control
expression or in VBA code.

--
Marsh
MVP [MS Access]

That makes sense. I never thought of that before - that Access can't
differentiate between a table/query field name and a form control name when
they are the same.
ctdak


"Marshall Barton" wrote:

> ctdak wrote:
>
> >You were right. That was the problem! The text box was named the same as
> >the field name ZipCode. Strange that this should matter though. When you
> >add a control for a field, Access automatically makes the control name and
> >the control source equal the field name, so why does the control name have to
> >be different when you change the control source to a formula using the field
> >name?
>
>
> If the control is just bound to a field, then you do not
> use the = sign and Access "knows" that it should get the
> field from that table/query and there is no ambiguity.
>
> In expressions, the confusion arises because it can refer to
> a field in the form's record source table/query or a control
> on the form by just using its name anywhere. There is no
> syntax to indicate which one you want to use. So, if you
> have a control named ZipCode and it has an expression like
> yours, then, Access thinks the expression's ZipCode is
> referring to the control named ZipCode, which is an infinite
> loop, thus the #Error.
>
> As far as Access defaulting the control name to the field
> name when you drag a field from the field list to the
> form/report, you've just joined a very long list of people
> with the same complaint. A lot of folks avoid this isuue by
> developing the habit of prefixing their control names with
> an abbreviation of the type of the control (e.g. txt, cbo,
> etc) whenever they refer to a control in a control
> expression or in VBA code.
>
> --
> Marsh
> MVP [MS Access]
>