Summing Based of DLookup Criteria in Form Footer

[mjseim]

I didn't think this would be such a big deal but gosh...

I have a very simple form based off of 4 fields from a table and 1 calculated field:
Field 1 (text) = ProductName
Field 2 (double) = UnitCount
Field 3 (double) = BuildingCount
Field 4 (memo) = Notes
Field 5 (calcualted) = calcProductType: DLookUp("[ProductType]","[List-ProductTypes]","[ProductName] = [Forms]![HOA-Associations-Subform-Products]![Product]")

So far so good. This correctly pulls the ProductType for the ProductName field shown in the table. The only values possible for ProductType are "SF" and "MF".

Now, all I want to do is have a few subtotals in the footer of my form. I want to sum all of UnitCount so long as calcProductType="SF" (for my total SF UnitCount). To do this I created the following formula:

=Sum(IIf([calcProductType]="SF",[UnitCount],0))

That's when I get an error. It just doesn't want to work. Instead of a calculated field within my query I tried putting the DLookup formula in an unbound field within the form but that produced no different results.

So what's the deal? Can I not sum values with criteria being a calculated value? Any help would be much appreciated.

Take care.

 

[ajetrumpet]

I want to sum all of UnitCount so long as calcProductType="SF" (for my total SF UnitCount). To do this I created the following formula:

=Sum(IIf([calcProductType]="SF",[UnitCount],0))

Instead, try this...

= DSum ("[unitcount]", "table", 

"[ProductType] = [Forms]![HOA-Associations-Subform-Products]![calcProductType]")

 

[mjseim]

But you see, that won't work because [ProductType] is not a field in "table". In an attempt to create a normalized database I thought I could avoid duplicating the data if I just stored the product types under a seperate table. So, I just pull in [ProductType] based off of [ProductName] whenever I need to.

Also, let me clarify something... I'm not getting errors with the =Sum(IIf([calcProductType]="SF",[UnitCount],0)) formula. I'm just getting zeros; perhaps this changes things?

 

[ajetrumpet]

But you see, that won't work because [ProductType] is not a field in "table". In an attempt to create a normalized database I thought I could avoid duplicating the data if I just stored the product types under a seperate table. So, I just pull in [ProductType] based off of [ProductName] whenever I need to.

That's OK. It sounds like you have a lookup table for product type. But regardless or weather or not the product name is IN "table" or not, the fact that this code...

DLookUp("[ProductType]","[List-ProductTypes]",

"[ProductName] = [Forms]![HOA-Associations-Subform-Products]![Product]")

DOES WORK for this...

Field 5 (calcualted) = calcProductType:

tells me that product name and product type are related SOMEHOW. They have to be, or else your lookup function would not work. So, in other words, you simply cannot have product types sitting separately in a table somewhere without a relationship to the DLookup criteria section (e.g. product name). This relationship could be set through either another field in your lookup table, or a relationship simply between tables, but it has to exist somewhere.

It may help in this case to post your file.