Get Value from query in a form unbound field without where condition

[hasanrazaj1]

hey dearz.

I have a query which includes 3 fields:

(1)- Sum of WithDrawalAmount
(2)- Sum of DepositAmount
(3)- BalanceAmount (Balance: [Sum Of Deposit Amount]-[Sum Of W/D Amount])



I have a bound Form including a unbound field name "GrandTotal"

I want to show query "BalanceAmount" value result in the Form GrandTotal field. how it is possible.

DLookup and ELookup funcations need the give where condition but i want to show the value without where condition.

[pbaldy]

As answered elsewhere, the DLookup "where" argument is not required.

[hasanrazaj1]

please give me example how to ignore where condition.

i try to use dlookup like this
=DLookup("[Balance]", "GrandTotalQuery") but it showing #Name? error.

and also try to use this
=DLookup("Balance", "GrandTotalQuery", "SumOfW/DAmount = " & Nz([SumOfW/DAmount],0)). but not working.

any suggession

[pbaldy]

The first should work:

=DLookup("[Balance]", "GrandTotalQuery")

You might try bracketing the query name too. Can you post the db if it still doesn't work?

[hasanrazaj1]

dear sir

first expression giving #name? error, also used brackets around query name but not working.

I try another method (i think may be it caused to corrupt the form anytime) in which i put the query as subform in main form where i need to display value.

now the main Form"Account" have two subforms like "Transcations" and "GrandTotalQuery".

then I refer the txt box (control source property) in main form to subform "GrandTotalQuery"s Balance Field. (like this:=[Forms]![Account]![GrandTotalQuerysubform].[Form]![Balance])
which is working well.


please suggest me which method is better

i am posting my db.

[pbaldy]

I am traveling for the holiday and working on a Mac, so no Access here. I'll check the sample when I'm on a PC with Access on it. Someone else may spot the problem in the meantime.

[pbaldy]

Back from vacation. I put a textbox on the form with a control source of:

=DLookup("[Balance]", "GrandTotalQuery")

and it returned the same -321 that your subform method does. You can use either but the subform method is a little "heavier" because of the subform.