ACCESS FEED: RE: [MS_AccessPros] Re: Query Not seen

Rabu, 10 April 2013

RE: [MS_AccessPros] Re: Query Not seen

Shekar-

You imply that you have two forms - one to display the query below (the
query isn't updatable) and another where you are changing the data while the
form showing the totals is open. If the total becomes zero, what you're
displaying with the query below shouldn't change unless you Requery the form
- and then that record should disappear.

What are you editing in the other form? Do you run any code after something
is updated in the other form?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of tchshekar
Sent: Wednesday, April 10, 2013 9:15 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Query Not seen

Query is Like this:-

SELECT InvSalesman.CustomerID, InvSalesman.SalesmanID,
InvSalesman.InvoiceNo, InvSalesman.Date, InvSalesman.Amount,
SumCollected1.TotalColl, SumCollected1.TotalWHT, SumCollected1.TotalCr,
SumCollected1.TotalEx, SumCollected1.TotalGA,
IIf(IsNull([TotalGA]),([Amount]),([Amount]-([TotalColl]+[TotalWHT]+[TotalCr]
+[TotalEx]))) AS Balance,
IIf(IsNull([TotalGA]),[Amount],([Amount]-([TotalColl]+[TotalCr]+[TotalEx])))
AS NetBal, SumCollected1.TotalOnACC
FROM InvSalesman LEFT JOIN SumCollected1 ON (InvSalesman.CustomerID =
SumCollected1.CustomerID) AND (InvSalesman.InvoiceNo =
SumCollected1.InvoiceNo)
GROUP BY InvSalesman.CustomerID, InvSalesman.SalesmanID,
InvSalesman.InvoiceNo, InvSalesman.Date, InvSalesman.Amount,
SumCollected1.TotalColl, SumCollected1.TotalWHT, SumCollected1.TotalCr,
SumCollected1.TotalEx, SumCollected1.TotalGA,
IIf(IsNull([TotalGA]),([Amount]),([Amount]-([TotalColl]+[TotalWHT]+[TotalCr]
+[TotalEx]))),
IIf(IsNull([TotalGA]),[Amount],([Amount]-([TotalColl]+[TotalCr]+[TotalEx])))
, SumCollected1.TotalOnACC
HAVING
(((IIf(IsNull([TotalGA]),([Amount]),([Amount]-([TotalColl]+[TotalWHT]+[Total
Cr]+[TotalEx])))) Is Null Or
(IIf(IsNull([TotalGA]),([Amount]),([Amount]-([TotalColl]+[TotalWHT]+[TotalCr
]+[TotalEx]))))>0));

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
<JohnV@...> wrote:
>
> Shekar-
>
>
>
> What is the SQL of your query? Are you saying when you set the balance to
0
> in the form that other fields go blank? That could be a result of the
> filter, but I would need to see the SQL to be sure.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of tchshekar
> Sent: Sunday, April 07, 2013 4:21 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] Query Not seen
>
>
>
>
>
>
> Hi
> I have made a query with customer, Invoice, Invoice Balance, which is
being
> pulled to Form, with criteria to show only those invoices which have
balance
> of more than 0,, but once the Balance of invoice becomes zero, the form
from
> where the entry is done Customer feild is left blank.
>
> KIndly help
>
> Thanks
>
> Shekar
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]