CODEWITHSUNDEEP
ms-access
JimT
JimT

Reputation: 169

MS Access Expression That Includes Dynamic Field Names

I have a crosstab query which returns results based on consumer demand for a bunch of material numbers. The material numbers become my field names in the crosstab query, and later the values from those fields are displayed in a form.

In the form, I display the value in a textbox. There are a couple of these textboxes where I need to sum the total of two or more values from these fields. Not a big deal it's a simple expression. For example (in the Control Source property): =[H123457] + [H123456].

This works well UNTIL there is no demand for a particular material number. In this case, the field doesn't show up in the crosstab query and I'm left trying to sum two fields where one doesn't exist.

I've tried IIf(IsError([H123456]), 0, [H123456]), Null expressions, Nz function, etc but cannot figure out how to dynamically solve the #Name issue that ends up populating the text box.

Essentially what I want is for a 0 value for the field that doesn't exist, so I can add it to the value where the field DOES exist - is this possible?

Regards!

Upvotes: 0

Views: 429

Answers (1)

JimT
JimT

Reputation: 169

June7 provided the answer in the allenbrowne.com link. Essentially, you need to add all of the possible field names to the Column Headings property in the crosstab query property window. Then it's a simple matter of adding an Nz() function to handle null values.

Thanks June7!

Upvotes: 0

Related Questions