Re: Expression that won't do a formula, acts as Parameter

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Tom

First of all, thanks so much for the help. It was such a relief to check in
this morning and see another post. But, I'm still having troubles. If I use
the expression builder, the text box stays unbound, and the formula won't
display. If I type it into the text box directly, I get "#Error" on the
report. Can you tell from that info what I'm messing up now?

Thanks Again

Michelle


"Tom Wickerath" wrote:

Hi Michelle,

You cannot reference the Kin's Total and Kin's payment total queries in this
manner. You have a couple of choices here:

1) Use the Domain Aggregrate Function DSum. For example:
=DSum("FieldName", "TableOrQueryName", "OptionalCriteria")

In your case, since you are referencing two sums, invoice total and payment
total, you can string two DSum statements together. Something like this, but
all on one line in the control source for the textbox:

=DSum("PaymentsFieldName", "TableOrQueryName", "OptionalCriteria") -
DSum("InvoicesFieldName", "TableOrQueryName", "OptionalCriteria")

If the payment and invoice data is in the same field, then the first example
should do the trick. See the following page on the MVPS web site for more
help on using Domain Aggregrate functions:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

2) The second option, which is not as easy to implement, would involve using
a subreport to display the individual records that make up each sum (you can
hide the detail section). Then, in the footer of the subreport, you have text
boxes that sum the various quantities. You can then use an expression to
display these values on your main report.

I think since you indicated that you are rusty with Access, I'd stick with
using the Domain Aggregrate Function method. Hoping you did not have Access
nightmares...


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Michelle" wrote:

Hi Linq

Kin's Total and Kin's payment total are both queries that do nothing but add
up all the invoices and all the payments. I used the expression builder and
double clicked them from the list in the builder. The expression is in a
text box in a report footer. Does that make any sense?

Thanks Again

Michelle
.

Relevant Pages

  • Re: Business objects, subset of collection
    ... from invoice i join payment p on i.invoiceid=p.invoiceid ... locating an invoice. ... composite key based on the sum of all of the attributes and methods at ... A SQL database is very good at doing thing kind of decisions. ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... from invoice i join payment p on i.invoiceid=p.invoiceid ... locating an invoice. ... composite key based on the sum of all of the attributes and methods at ... A SQL database is very good at doing thing kind of decisions. ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... with amount due, payment total, etc. ... for the selected vendor ... I think one way to express my point is that the filter is the WHERE clause and one specifies the attributeto be checked in it. ...
    (comp.object)
  • Re: credit card reciept response
    ... transaction is submitted for processing. ... to the consumer or a POST string to a site designated by the merchant. ... Here you accept the POST data, validate it, and mark the invoice ... DO NOT send any order details back to the payment ...
    (comp.lang.php)
  • Re: Getting Data from 2 Queries into 1 Report
    ... the Payment in a one-to-many relationship. ... to something else that generates your actual invoice and that you may ... the Primary Key of the 'one' table. ... don't include any Lookup Fields! ...
    (microsoft.public.access.gettingstarted)