435,571 Members | 3,168 Online
Need help? Post your question and get tips & solutions from a community of 435,571 IT Pros & Developers. It's quick & easy.

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

 P: 34 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 Feb 12 '12 #1
18 Replies

 100+ P: 189 You can simple use 'If' formula in the query. something like this : Expand|Select|Wrap|Line Numbers IIf([DRAMOUNT]>0,[DRAMOUNT],0) . Similarly for CRAMOUNT. This will serve your purpose. Feb 12 '12 #2

 P: 34 I am already use the similar Nz function as you can see below Expand|Select|Wrap|Line Numbers 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 Feb 12 '12 #3

 Expert Mod 15k+ P: 31,494 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 : Expand|Select|Wrap|Line Numbers Nz(Sum([DRAMOUNT]),0) AS BALANCEDR Feb 12 '12 #4

 P: 34 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 Feb 13 '12 #5

 Expert Mod 15k+ P: 31,494 Is there a good reason why you're asking that instead of trying out the solution already suggested? Feb 13 '12 #6

 P: 34 I try your suggestion as u can see below but still i have the same problem Expand|Select|Wrap|Line Numbers 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]; Feb 13 '12 #7

 Expert Mod 15k+ P: 31,494 Please read [code] Tags Must be Used while I look at what might be going wrong. Feb 13 '12 #8

 Expert Mod 15k+ P: 31,494 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. Feb 13 '12 #9

 P: 34 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 Feb 14 '12 #10

 Expert Mod 15k+ P: 31,494 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. Feb 14 '12 #11

 P: 34 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 : Expand|Select|Wrap|Line Numbers 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 Feb 14 '12 #12

 Expert Mod 15k+ P: 31,494 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. Feb 14 '12 #13

 P: 34 The SQL for the query [BALANCES ACCORDING TO SELECTED DATES]is Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers 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. Feb 14 '12 #14

 Expert Mod 15k+ P: 31,494 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) : Expand|Select|Wrap|Line Numbers 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 : Expand|Select|Wrap|Line Numbers 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] Feb 15 '12 #15

 P: 34 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 Expand|Select|Wrap|Line Numbers 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? Feb 19 '12 #16

 Expert Mod 15k+ P: 31,494 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 ;-) Feb 20 '12 #17

 P: 34 Thank u so much, it works perfectly now. All my regards from the sunshine Cyprus :-) Feb 20 '12 #18

 Expert Mod 15k+ P: 31,494 You're welcome Anna from Sunny Cyprus :-) Feb 20 '12 #19