"#type!" error on report calculated field

Question

I have a report in Access with 3 fields on it: Width, Height and Area.
Width and Height are pulled from the table the report is bound to, whilst Area should be calculated (height * width). I've set the Control Source of the Area to = [Height] * [Width], but on opening the form the field displays #Type!, in typically descriptive Access errors fashion, with nice use of # and ! to make it impossible to accurately Google... but I digress. I have no idea what #Type! means and Access doesn't want to tell me.

I can't understand this. In the bound table, Height and Width are Integers, and are both populated in the record being viewed (so it's not a NULL problem). If I change the Control Source to something really simple - like =[Height], it spits out #Error! instead (again, thanks for the useful intel, Access. We'd be lost without you). Even = 1 spits out #Error!.

Any idea why Access hates my control sources?

Humiliating

Answer 1

You probably have a name clash, i.e. Access is picking up the Width and Height members of the report object rather than the fields called Width and Height.

I would create a new query and simply rename the problematic fields in it. So, if you're using the query designer:

• add the primary key and any other non-problematic field, then WidthValue: Width and HeightValue: Height as additional columns;
• finally, set the report's record source to be the query rather than the table directly, and update the calculated control formulae accordingly.

Hyperpituitarism

@Humiliating I agree with Chris, it's probably a name conflict. Microsoft has a pretty good page of possible #Error causes and solutions and if you scroll down a bit and you'll see the a section on fixing the circular reference issue titled "Check for a name conflict". – Scandura

Nice. Think you're right about the name conflict. Still weird that it #Errors with a control source of = 1 though. Not that it matters terribly. – Humiliating

Unfortunately, the useful-looking link to Microsoft's page of possible #Error causes and solutions in @Blackhawk's comment appears not to work any more. I just get CoPilot asking me what I want to know, but it doesn't appear very helpful so far... – Halcomb

@Halcomb A quick bit of poking around the wayback machine gives: web.archive.org/web/20140207020142/http://office.microsoft.com/… – Scandura

Answer 2

This is a pretty simplistic calculation. Any reason why you don't create a query, do the calculation in said query, and bind the report to the query instead? No sense killing yourself trying to figure this out.

In fact, now that I'm thinking about it, Height and Width are probably reserved words, as they're properties of a control. Maybe change them to HHeight and WWidth or something?

Herries

+1 for the principle of calculating this sort of thing in the query - a really useful tip for us Access-newbies – Greatnephew

Answer 3

When access created a report it uses the field name of queries to create the name of related control on the report. then if you use the query field names later it actually refers to the report control with the same name. so solution, either rename the report field name to something different from query fields or fully qualify the query field name [query].[field] to force access to use the query field name.

Beckerman

Answer 4

Microsoft does not list Height and Width as reserved words, however, they are used when sizing forms and reports.

Willie

Is it an answer? – Cirillo

Answer 5

I had a related problem where my Form would give me this error. Changing the Form property Data Entry to Yes solved this issue. Not sure that this solves your exact issue, but perhaps in the Report Properties you can find a related field?

Durr

This response is more suited as a comment than an answer. – Kirchner

@RahulBhobe FYI, you need 50 rep to post a comment. – Kopple

Then one should wait for 50 reps before commenting :). – Kirchner

Answer 6

right click on your AREA filed and select properties then go to EVENT tab then click on "ON ENTER" select from drop down list [Event Procedure] and click on the dots on the right side and go to "MICROSOFT VISUAL BASIC FOR APPLICATION" on this window type this code above the "End Sub"

Area = Height * Width

but attention the name you enter on this code must be able on your database and as the same as you things that type in the code line good luck

Hogweed