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

[Michelle]

I'm using Access 2002, and haven't worked in it for years so I'm really
rusty. I'm setting up a simple accounts receivable database that will print
statements. I've finally made it to the report stage. I have 2 separate
queries that add up the total invoices and total payments. When I try to do
a simple formula to subtract the payments from the invoices:

=[Kin's Total]![Sum of Invoice Total]-[Kin's payment total]![Sum of Cheque
Amount]

It behaves like I'm asking for parameters. The report does work on date
parameters, so when I run the report, it asks for the start and end date of
the report and then asks for Kin's Total and Kin's payment total. How do I
change this from a parameter to a calculcated formula.

Thanks for any help you can offer.

Michelle

 

[Linq Adams via AccessMonster.com]

Where exactly do you have this formula and what is

***Kin's Total*** and ***Kin's payment total***

The fact that Access is asking for them means it can't find them. I suspect
something is wrong in the way you're referencing them.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Michelle]

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

 

[Michelle]

Hi Again Linq

I've been struggling with this all night, and it's either cry or go to bed
at this point. I think I'm going with bed. But, I've been trying different
formulas and levels of formulas. If I try setting the text box to be equal
to just a field in one of the queries, with no operators, it still behaves
like a parameter query. With a formula in the control source, it still
prompts me for "Kin's Total" and "Kin's Payment Total". If I enter a number
in the prompts, I get an #Error message for that field in the report. By the
way, I tried renaming the queries so there's no spaces or punctuation in the
query name, and that didn't make any difference either.

Here's hoping I don't have Access nightmares,

Michelle

 

[Tom Wickerath]

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]

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]

Hi Michelle,

I would just type the expression in directly, as you have attempted to do,
rather than attempt to use the expression builder. However, in order to help
you, I would need to see the control source that you attempted to enter.

What is the name of the text box control that you attempted to enter your
expression into? It cannot be the same name as a field in the report's
recordsource, otherwise you will get an error. Try adding the lowercase "txt"
prefix to the name of this textbox. For example: txtBalanceDue.

Perhaps this Knowledge Base (KB) article will be helpful to you:

(ACC2000 Troubleshooting Tips for Error Values
http://support.microsoft.com/?id=209132

Note: I added parenthesis around the "ACC2000:" part, because this article
applies equally well to other versions of Access.


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

 

[Michelle]

Hi Again

OK, I've tried a couple of different versions, and I still keep getting the
error message. I hadn't changed the name of the text box, just left it as
"text25". I would like to be able to use the criteria to pick out just the
customer the statement is for. Even if I take the criteria out, I still get
the error message, so I'm guessing it's something to do with the way I've
specified the fields and table. The table name I want to pull from is
Invoices, the field I want to add up is Invoice Total.

Here's the formula I typed directly into the text box.

=DSum("[Invoices]","[Invoice Total]")

The field that has the customer name is called Location. If I wanted to
pick out the particular customer, would it read like this?

=DSum("[Invoices]","[Invoice Total]","[Location]='CAL'")

Undying Gratitude

Michelle

 

[Tom Wickerath]

Hi Michelle,

The table name I want to pull from is Invoices, the field I want to add up
is Invoice Total.

Here's the formula I typed directly into the text box.

=DSum("[Invoices]","[Invoice Total]")

You have the parameters switched around. Try this:

=DSum("[Invoice Total]", "[Invoices]")

The general form is:

=DFunctionName("FieldName", "TableOrQueryName", "OptionalCriteria")


Try this:

=DSum("[Invoice Total]","[Invoices]","[Location]='CAL'")

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

Hi Again

OK, I've tried a couple of different versions, and I still keep getting the
error message. I hadn't changed the name of the text box, just left it as
"text25". I would like to be able to use the criteria to pick out just the
customer the statement is for. Even if I take the criteria out, I still get
the error message, so I'm guessing it's something to do with the way I've
specified the fields and table. The table name I want to pull from is
Invoices, the field I want to add up is Invoice Total.

Here's the formula I typed directly into the text box.

=DSum("[Invoices]","[Invoice Total]")

The field that has the customer name is called Location. If I wanted to
pick out the particular customer, would it read like this?

=DSum("[Invoices]","[Invoice Total]","[Location]='CAL'")

Undying Gratitude

Michelle

 

[Michelle]

Good Grief. Save me from myself. I switched them around and it works now.
You deserve all the riches for spending your Sunday helping the Microsoft
impaired like me.


Michelle

Hi Michelle,

The table name I want to pull from is Invoices, the field I want to add up
is Invoice Total.

Here's the formula I typed directly into the text box.

=DSum("[Invoices]","[Invoice Total]")

You have the parameters switched around. Try this:

=DSum("[Invoice Total]", "[Invoices]")

The general form is:

=DFunctionName("FieldName", "TableOrQueryName", "OptionalCriteria")


Try this:

=DSum("[Invoice Total]","[Invoices]","[Location]='CAL'")

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

Hi Again

OK, I've tried a couple of different versions, and I still keep getting the
error message. I hadn't changed the name of the text box, just left it as
"text25". I would like to be able to use the criteria to pick out just the
customer the statement is for. Even if I take the criteria out, I still get
the error message, so I'm guessing it's something to do with the way I've
specified the fields and table. The table name I want to pull from is
Invoices, the field I want to add up is Invoice Total.

Here's the formula I typed directly into the text box.

=DSum("[Invoices]","[Invoice Total]")

The field that has the customer name is called Location. If I wanted to
pick out the particular customer, would it read like this?

=DSum("[Invoices]","[Invoice Total]","[Location]='CAL'")

Undying Gratitude

Michelle

 

[Tom Wickerath]

<Smile>

Glad to see that you got it working!


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