Trying to Display a Sum on a Form

[lahatte]

Using Access 2003, I am trying to display the sum of values in a field from a query on a form, and the box say #error most of the time, and I have no idea how to determine what the 'error' is.

I have a query called "Jobs Not Paid" which returns some info on jobs that have been invoiced but not paid. One of the columns in the query results is "Job_Billed_Amount". I want to display a sum of the values in this column on my form. This will be the total amount that we are currently owed.

The way I see this; I can either do the summing via the control on the form, or use a separate query to sum the values in the "Jobs Not Paid" query, and put that result on the form. I haven't had any luck either way.

I have made a text box on the form and tried setting the 'control source' to
=sum([Jobs Not Paid]!Job_Billed_Amount), but the textbox just says #error. If I just put "=sum(Job_Billed_Amount)", which I assume is now referencing my main data table (which also has a field with that name), the total of that field for ALL records is displayed in the textbox, but of course I only want the total of those records 'invoiced but not paid'.

Please help!

Thanks.

 

[RainLover]

You need to sum the value of a Field in the Query.

If that field has null values then you will get an error.

 

[lahatte]

Thanks for the response.

I have tried two ways of doing this. One is to use the sum function as I mentioned to sum the values via the "Expression Builder" via the properties for the textbox on the form. The second is to use a 'summing' query that, when run, creates a virtual table with one cell containing the sum. I then put a reference to that query as the control source for the textbox, as in... =[Not Paid Query Sum]!SumOfJob_Billed_Amount. The summing query works on its own, but I cannot get the sum into a textbox on a form.

Please, if you can, provide an expression for the 'control source' that will accomplish this seemingly simple task.

Thanks!

 

[lahatte]

Could it possibly be that the 'control source' property for the textbox on the form is not able to understand that it should use the specified query as the source, rather than the form's source, which is a table? If so, how can I make the 'control source' property for the textbox look to the query? I thought I was doing that, but maybe not.

 

[RainLover]

In the Control Source of the Control on the Form place;

=Sum([FieldNameFromQuery])

Perhaps it is more correct to have;

=Sum([FieldNameFromRecordSource])

 

[lahatte]

I think the problem is that Access forms have one source and all controls on a form must use that one source. The only way I've found to get information from another source is to use a subform and make it use the additional source. But that sucks bigtime.

 

[RainLover]

So are you saying that the Value you wish to Sum is not part of the RecordSource of the Form.

 

[lahatte]

Yes. The values I want to sum are from a query. The form's source is a table.

The query looks at my table, picks out only those record which are marked as 'invoiced' but not yet marked as 'paid', and outputs the requested information matching those requirements.

If I could figure out how to make such a complex expression via "Expression Builder", and have it keep a running total of the Job_Billed_Amount field for the matching records, I could get a sum that way. I haven't come up with that yet either.

Thanks.

 

[RainLover]

I am not exactly sure what you are doing here.

Could you post a cut down version of your Database.

No later than 2003.

 

[lahatte]

I might be able to post some of the database, but it likely wouldn't help.

This aspect, I think, is pretty straight forward...

The form is used to interface between the user and the data table... to enter and display the data.

The query just lets me know who has been invoiced but from whom I haven't yet received payment. The query simply makes a list of those records from the main table, and one of the fields returned by the query is "Job_Billed_Amount". If I sum up the values in the query's "Job_Billed_Amount" field, well, that's the sum I'm after. I want to know the total of all outstanding invoices.

Does that help?

Thanks.

 

[RainLover]

If you have the Values in the Query and that Query is the RecordSource of the Form then what I have posted will work.

What am I missing here?

Other than that you may need to use DSum.

 

[lahatte]

The query is NOT the record source of the form. The source for the form is a table.

Thanks.

 

[RainLover]

>The source for the form is a table<

That being the case, Sum the Field in the Table.

 

[lahatte]

That isn't the sum I need. I need the sum from the values returned in the query. The query selectively pulled records from the table. I don't need a sum of all the table records.

 

[RainLover]

You cannot have two different RecordSources.

Create a query for the Form then Sum the values in the Query.

If this is not possible then you need to use DSum Function.

 

[lahatte]

Can you explain what you mean by "create a query for the form"? I already have a query that gives me the records, and I also made one that sums those records and the resulting virtual table simply has one cell containing the sum.

I just started looking at DSUM. I don't know if it will do what I need either. No luck with it yet.

 

[RainLover]

I really don't have enough information to solve your problem, but I will try again.

The "Sum" function only works on the RecordSource of the Form (or a Report). This can be either a Table or a Query.

You cannot have a Table as the RecordSource and then try to Sum a Value in a Query. It just does not work.

One alternative is to use a Domain Function, in this case it would be DSum.

The DSum Function will give you the Sum of any field in a Table or Query. This function can be tricky to write, but not overly difficult.

Have a look at the DSUM help and try to sum the values in the query.

My advise here may not be correct as I can't see exactly what you are doing.

If you posted a cut down version of your Database with just the One Table and Query in question with only two or three records I could help further. But at the moment I am making assumptions of which I cannot be sure of.

 

[lahatte]

Thanks. I'll see about getting a database sample posted when I get a chance.

 

[RainLover]

When you are ready is fine by me.

 

[hapmirsky]

I don't use the .adp and neither do many other people which is why it has been deprecated.