How can i have zero values in 2 fields when the criteria in a third field is false

[annakalli]

Hello, am having a table called TRANSACTIONS, this table contains 3 fields : TRANSACTION DATE, DRAMOUNT and CRAMOUNT.

I create a Query that ask for a starting transaction date and then shows the totals for the DRAMOUNT and CRAMOUNT for the previous transactions dates.

If i have transactions before the starting date everything is ok but if i don't, i get no results in my query while i want to get zero values.

can someone please help

[Narender Sagar]

You can simple use 'If' formula in the query. something like this :

IIf([DRAMOUNT]>0,[DRAMOUNT],0)

. Similarly for CRAMOUNT. This will serve your purpose.

[annakalli]

I am already use the similar Nz function as you can see below

SELECT   TBLBANKSTRANSACTIONS.[BANK CODE]
       , Sum(Nz([DRAMOUNT],0)) AS BALANCEDR
       , Sum(Nz([CRAMOUNT],0)) AS BALANCECR
FROM     TBLBANKSTRANSACTIONS
WHERE (((TBLBANKSTRANSACTIONS.[TRANSACTION DATE])<[forms]![Report Date Range]![Beginning Trans Date]))
GROUP BY TBLBANKSTRANSACTIONS.[BANK CODE];

I try your advise but i get an error message
any more ideas

[NeoPa]

The Sum() function already handles null values, but I believe still returns Null if there are no records processed. Thus, the Nz() call should be outside of the Sum() call as :

Nz(Sum([DRAMOUNT]),0) AS BALANCEDR

[annakalli]

The problem is that when i run the query and i don't have any transactions for the days before the transaction date that i enter i get as an answer just the titles of the field names, which is correct but can i get the values zero instead

[NeoPa]

Is there a good reason why you're asking that instead of trying out the solution already suggested?

[annakalli]

I try your suggestion as u can see below but still i have the same problem

SELECT TBLBANKSTRANSACTIONS.[BANK CODE], Nz(Sum([DRAMOUNT]),0) AS BALANCEDR, Nz(Sum([crAMOUNT]),0) AS BALANCECR
FROM TBLBANKSTRANSACTIONS
WHERE (((TBLBANKSTRANSACTIONS.[TRANSACTION DATE])<[forms]![Report Date Range]![Beginning Trans Date]))
GROUP BY TBLBANKSTRANSACTIONS.[BANK CODE];

[NeoPa]

Please read [code] Tags Must be Used while I look at what might be going wrong.

[NeoPa]

Are you simply opening this query, or are you displaying the results somewhere else (like on a form)?

Clearly, if there are no records, there will be nothing shown. That's basic. That you want something to display confuses, and leads me to believe there is still much of the question you haven't explained yet.

[annakalli]

This query is used in a report. When i have transactions, my report works OK. For example if i asked to display all the transactions according to a starting date - ending date the report displays firstly a field called BALANCE which is the subtraction of the field BALANCEDR -BALANCECR, this figure is for the days before my starting date and it comes from that query and then rest of the figures for the given days which are from the table TBLBANKSTRANSACTIONS. However if i don't have any figures in the fields BALANCEDR, BALANCECR but still i have transactions for the period that i define in my starting date - ending date my report displays nothing. I believe that if in this case i was having as a value in the field BALANCE the value zero, the report will be ok

[NeoPa]

Ah. We seem to be getting somewhere near whjat the question should have been.

How is the control [BALANCE] defined? How do you get the result from the SQL query from post #7 to be reflected in there?

How do you refer to this control later? It may be that the reference simply needs to use the Nz() function, but if you want the [BALANCE] value to display zero as well then that wouldn't be a good enough solution.

[annakalli]

In my report i add the query BALANCES ACCORDING TO SELECTED DATES that contains the 3 fields,[BALANCEDR], [BALANCECR], [bank code]. I join this query with my table TBLBANKSTRANSACTIONS through the field [bank code]. I then create a calculated field which is called [balance]. I define the field as :

balance: Nz([TOTAL-BALANCES ACCORDING TO SELECTED DATES].[BALANCEDR]-[BALANCECR],0)

Yes, i want the [BALANCE] value to display zero, if i don't have any transactions in the query BALANCES ACCORDING TO SELECTED DATES so i can run my report

[NeoPa]

First, please look at [code] Tags Must be Used and make sure you use them in all future posts containing code.

I need to see what is in the RecordSource of your form. If it's a query then I need to see the SQL of that query. Your response was a good one, but I need more information than you replied with to understand exactly what you're dealing with. I may also need the SQL for [BALANCES ACCORDING TO SELECTED DATES], so please include that too.

I suspect the answer will end up including some sort of UNION clause in the SQL somewhere, but I need the extra info to determine exactly where.

[annakalli]

The SQL for the query [BALANCES ACCORDING TO SELECTED DATES]is

SELECT TBLBANKSTRANSACTIONS.[BANK CODE], Sum(Nz([DRAMOUNT],0)) AS BALANCEDR, Sum(Nz([CRAMOUNT],0)) AS BALANCECR
FROM TBLBANKSTRANSACTIONS
WHERE (((TBLBANKSTRANSACTIONS.[TRANSACTION DATE])<[forms]![Report Date Range]![Beginning Trans Date]))
GROUP BY TBLBANKSTRANSACTIONS.[BANK CODE];
 

The RecordSource of my report is called TBLBANKS_TRANSACTIONS and the SQL of that is the following

SELECT TBLBANKSTRANSACTIONS.ID, TBLBANKSTRANSACTIONS.[TRANSACTION NUMBER], TBLBANKSTRANSACTIONS.[TRANSACTION DATE], TBLBANKSTRANSACTIONS.[AR EGGRAFOU], TBLBANKSTRANSACTIONS.DESCRIPTION, TBLBANKSTRANSACTIONS.DRAMOUNT, TBLBANKSTRANSACTIONS.CRAMOUNT, TBLBANKSTRANSACTIONS.[TYPE OF TRANSACTION], TBLBANKSTRANSACTIONS.[BANK CODE], Nz([TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCEDR-[BALANCECR],0) AS [balance], [TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCEDR, [TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCECR
FROM TBLBANKSTRANSACTIONS INNER JOIN [TOTAL-BALANCES ACCORDING TO SELECTED DATES] ON TBLBANKSTRANSACTIONS.[BANK CODE] = [TOTAL-BALANCES ACCORDING TO SELECTED DATES].[BANK CODE]
WHERE (((TBLBANKSTRANSACTIONS.[TRANSACTION DATE])>=[forms]![Report Date Range]![Beginning Trans Date] And (TBLBANKSTRANSACTIONS.[TRANSACTION DATE])<=[Forms]![Report Date Range]![Ending Trans Date]) AND ((TBLBANKSTRANSACTIONS.[BANK CODE])=[ΠΑΡΑΚΑΛΩ ΔΩΣΕ ΚΩΔΙΚΟ]) AND (([TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCEDR)<[forms]![Report Date Range]![Beginning Trans Date]) AND (([TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCECR)<[forms]![Report Date Range]![Beginning Trans Date]))
ORDER BY TBLBANKSTRANSACTIONS.[TRANSACTION DATE];
 

I hope i used the correct [code] Tags Must be Used, this time. Thank u in advance for your efforts to help me.

[NeoPa]

I guess your query is probably [TOTAL-BALANCES ACCORDING TO SELECTED DATES] rather than [BALANCES ACCORDING TO SELECTED DATES] as you stated in your post. If not then let me know as I'm going to assume it is (otherwise the query [BALANCES ACCORDING TO SELECTED DATES] is never used and nothing makes sense).

First of all, an INNER JOIN, such as you have, will ensure nothing is included for [BANK CODE]s where no records precede the [Beginning Trans Date] control. We need to change this to a LEFT JOIN.

Next, we need to handle the situation where there are no records and ensure that zeroes are returned anyway. This is not done in [TOTAL-BALANCES ACCORDING TO SELECTED DATES], but rather in [TBLBANKS_TRANSACTIONS]. Each reference to [TOTAL-BALANCES ACCORDING TO SELECTED DATES] needs to use Nz() around it here. Not in the source.

So, we have (I've added some other changes, mostly to the layout, just to make everything more readable) :

SELECT   tBT.ID
       , tBT.[TRANSACTION NUMBER]
       , tBT.[TRANSACTION DATE]
       , tBT.[AR EGGRAFOU]
       , tBT.DESCRIPTION
       , tBT.DRAMOUNT
       , tBT.CRAMOUNT
       , tBT.[TYPE OF TRANSACTION]
       , tBT.[BANK CODE]
       , Nz(qBals.BALANCEDR-qBals.BALANCECR,0) AS [balance]
       , Nz(qBals.BALANCEDR,0) AS [BalanceDR]
       , Nz(qBals.BALANCECR,0) AS [BalanceCR]
FROM     [TBLBANKSTRANSACTIONS] AS [tBT]
         INNER JOIN
         [TOTAL-BALANCES ACCORDING TO SELECTED DATES] AS [qBals]
  ON     tBT.[BANK CODE] = qBals.[BANK CODE]
WHERE    ((tBT.[TRANSACTION DATE] Between [Forms]![Report Date Range]![Beginning Trans Date] And [Forms]![Report Date Range]![Ending Trans Date])
  AND    (tBT.[BANK CODE]=[???????O ?OS? ?O????])
  AND    (qBals.BALANCEDR<[forms]![Report Date Range]![Beginning Trans Date])
  AND    (qBals.BALANCECR<[forms]![Report Date Range]![Beginning Trans Date]))
ORDER BY tBT.[TRANSACTION DATE]

NB. Lines #18 through #20 look fundamentally wrong to me. I can't be sure, but I would expect that all three lines should go (which would also mean losing the opening parenthesis "(" of line #17 of course) - leaving :

WHERE    (tBT.[TRANSACTION DATE] Between [Forms]![Report Date Range]![Beginning Trans Date] And [Forms]![Report Date Range]![Ending Trans Date])
ORDER BY tBT.[TRANSACTION DATE]

[annakalli]

You was right my code was a bit confusing in those lines so i follow your advice and i changed is as you can see below

SELECT TBLBANKSTRANSACTIONS.ID, TBLBANKSTRANSACTIONS.[TRANSACTION NUMBER], TBLBANKSTRANSACTIONS.[TRANSACTION DATE], TBLBANKSTRANSACTIONS.[AR EGGRAFOU], TBLBANKSTRANSACTIONS.DESCRIPTION, TBLBANKSTRANSACTIONS.DRAMOUNT, TBLBANKSTRANSACTIONS.CRAMOUNT, TBLBANKSTRANSACTIONS.[TYPE OF TRANSACTION], TBLBANKSTRANSACTIONS.[BANK CODE], Nz([TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCEDR-BALANCECR,0) AS [balance], [TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCEDR, [TOTAL-BALANCES ACCORDING TO SELECTED DATES].BALANCECR
FROM TBLBANKSTRANSACTIONS LEFT JOIN [TOTAL-BALANCES ACCORDING TO SELECTED DATES] ON TBLBANKSTRANSACTIONS.[BANK CODE]=[TOTAL-BALANCES ACCORDING TO SELECTED DATES].[BANK CODE]
WHERE (TBLBANKSTRANSACTIONS.[TRANSACTION DATE] Between Forms![Report Date Range]![Beginning Trans Date] And Forms![Report Date Range]![Ending Trans Date]) And ((TBLBANKSTRANSACTIONS.[BANK CODE])=[Please type the bank code]);
 

I can understand what do you mean by your next tip
"This is not done in [TOTAL-BALANCES ACCORDING TO SELECTED DATES], but rather in [TBLBANKS_TRANSACTIONS]. Each reference to [TOTAL-BALANCES ACCORDING TO SELECTED DATES] needs to use Nz() around it here. Not in the source."

how can i do that?

[NeoPa]

AnnaKalli:
how can i do that?

If you look at lines #10 through #12 of my code you'll see I've already done it for you ;-)

[annakalli]

Thank u so much, it works perfectly now. All my regards from the sunshine Cyprus :-)

[NeoPa]

You're welcome Anna from Sunny Cyprus :-)