Subform Math Question

[fenhow]

Hi,

I have a subform that has two cols in it.

Acres and Status

In the header of the sub-form I would like to show the following:

Total Acres where Status is = to Good to Go.

I was thinking I could put a unbound txtbox on there but am not sure of what to put as the control source.

Can anyone help me?

Thanks.

Fen How

 

[fenhow]

So far I have this to work but I cannot figure out how to only add those records that are Good to Go.

=Sum([si_Grantee_Acres])

Thanks.

 

[The_Doc_Man]

usually, you bind the form to a query that selects for your given status (... WHERE GOOD_TO_GO = TRUE...) rather than looking at the table.

 

[fenhow]

Thanks, I agree with that however in my form I want to show all of the records not just the ones that are Good To Go.

Can I still show all records but only sum the Good to Gos in the Query?

Thanks.

Fen

 

[The_Doc_Man]

yes but then the sum must be separately computed as eg using a DSUM with a non-trivial criteria argument that does the filtration. With conflicting display vs. computation requirements, you cannot use the same recordsource for both activities.

 

[fenhow]

Ok, so in my Query I would add an expression say:

SumOfsi_Grantee_Acres: Sum([tbl_siSurface_Seismic].[si_Grantee_Acress])

This does sum them up but they are not filtered. Can you show me how to use the DSUM in this situation?

Thanks.

 

[Mike375]

I use DSum and a query that is copy of the query for the sub form but with the MainForm ID as its criteria.

=DSum("[RegPremium]","Query313DSummer","[CoverageType] = 'loiben'")

Query313 supports the sub form. Anytime I make a query for a subform I also add one for D purposes

 

[fenhow]

So I would use two queries, but call the results from the copied query into the main query.

Fen

 

[fenhow]

Thanks I got it to work, any way I can show a 0 (Zero) if no matches?

Fen

 

[Mike375]

Just do DSum in unbound textbox

 

[fenhow]

Ok, this works but when I add this code to an unbound text box on my form it still shows blank. Any idea what I am doing wrong?

=DSum("[si_Grantee_Acres]","qry_siDSUMSurfaceAreaPermittedOnly","[si_Permitting_Status_Lookup] = 'Permitted'")

 

[The_Doc_Man]

Sorry, should have been clearer. When you can add something in the same query, use an "ordinary" aggregate. When you need to have different criteria than your main query, look to DOMAIN aggregates such as DSUM.

SUM() is a query-based aggregate function that can occur in an SQL query but the query must include a GROUP BY or other mechanism to pre-order the material being drawn into the query.

DSUM(...) is a Domain Aggregate function that can occur in code, forms, queries, and certain other contexts but isn't very efficient in queries. Because a DSUM and all other domain aggregates IMPLY a query with each call. On a form, they are not that bad. In limited-execution VBA, they are also useful. In a report's detail section, they can be seriously ugly because of the implied query that gets executed once per detail element.

 

[Mike375]

Ok, this works but when I add this code to an unbound text box on my form it still shows blank. Any idea what I am doing wrong?

=DSum("[si_Grantee_Acres]","qry_siDSUMSurfaceAreaPermittedOnly","[si_Permitting_Status_Lookup] = 'Permitted'")

It will show blank if there is nothing to meet the criteria.

As a check, open the query from the data base window while the main form is open. The query should display the same records as the sub form is showing for that particular record in the main form.