Help with expression

[myxmaster]

I have this expression that I entered in a experiment database and it
works great.

=DSum("[Amount]","YourTable","[TransactionType] =""Credit""")-
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""))

I am now trying to modify it to work in my main database however I am
having problems.I get a error message on the form and then it locks up
while calculating

=DSum("[Check Amount]","Transactions","[TransactionType] =""Credit""")-
NZ(DSum("[Check Amount]","Transactions","[Status] =""Cleared"""))

The form name is transactions1
The table is Transactions
The field is now called Check Amount

TIA
Jerry

 

[Al Campagna]

I get a error message on the form
What's the error message?
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[BruceM]

What happens when you run either of the DSum statements by themselves? How
about if you use 0 as the value if null in the Nz statement?
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""),0)
I wonder if you would do better with the calculation in a Totals query
rather than with a domain aggregate function. The only way I can think of
doing that is with two separate Totals queries that are combined into a
third query, but I am not sufficiently skilled in SQL to know if I am
overlooking a better way to do that.

 

[myxmaster]

Actually the ubound text box displays #Error

What's the error message?
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have this expression that I entered in a experiment database and it
works great.

=DSum("[Amount]","YourTable","[TransactionType] =""Credit""")-
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""))

I am now trying to modify it to work in my main database however I am
having problems.I get a error message on the form and then it locks up
while calculating

=DSum("[Check Amount]","Transactions","[TransactionType] =""Credit""")-
NZ(DSum("[Check Amount]","Transactions","[Status] =""Cleared"""))

The form name is transactions1
The table is Transactions
The field is now called Check Amount

TIA
Jerry

 

[Al Campagna]

OK... "error" could be any error. #Error means the calculation can not be evaluated.
Similar to #Name, which means an element of the calculation can not be found by Access.

1. Did you go back to your table design and change the field [Amount] to [Check Amount]?
(you should avoid spaces in object names)
2. You checked the query behind the form, and [Check Amount] is legitimate field delivered
to the form?
3. And... [Check Amount] on the form is a "bound" field, NOT a calculated field like
example... ([Check Amount] = SomeValue *2)?
4. Make sure you don't name the calculated field after any item in the calculation. NOT
[Check Amount], or [Status]. etc..

I agree with Bruce... try the Credit portion only... then try the Cleared portion only. I
don't see any obvious syntax problem, so if neither DSum works standalone, then there's
something wrong with the [Check Amount] field itself.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Actually the ubound text box displays #Error

I get a error message on the form

What's the error message?
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have this expression that I entered in a experiment database and it
works great.

=DSum("[Amount]","YourTable","[TransactionType] =""Credit""")-
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""))

I am now trying to modify it to work in my main database however I am
having problems.I get a error message on the form and then it locks up
while calculating

=DSum("[Check Amount]","Transactions","[TransactionType] =""Credit""")-
NZ(DSum("[Check Amount]","Transactions","[Status] =""Cleared"""))

The form name is transactions1
The table is Transactions
The field is now called Check Amount

TIA
Jerry