SQL Statement IF EXIST(SELECT THEN ELSE) on MS ACCESS

Question

I have problem to show "NOT PAID" in MS Access SQL Statement:-

I using Nz and IIF functions but not get what I wants.

(Select Nz(m.amount, "NOT PAID")
 From monthly m
 Where  m.year = s.year
        AND m.month='February') As PayFeb

You can refer related this question.

This only show amount if record found, but PayFeb will empty if Record Not Found where it's should be show "NOT PAID".

Please help me how to solve this matter.

score 0 · Answer 1 · answered Oct 07 '15 at 06:57

0

To use the IIF function, there should be a condition, and I can't find any condition in your code. Please try the below;

Select iif( m.amount>0, m.amount, "NOT PAID")
From   monthly m
Where  m.year = s.year 
And    m.month='February'

answered Oct 07 '15 at 06:57

Nadeem_MK

7,533
7
50
61

Hello Nadeem_MK Thanks you very much for your time to reply my question. But, when I test solution above on MS ACCESS IDE result still same. PAY FEB are empty value where suppose "NOT PAID". Do you think because no data on FEBRUARY that why this happen? – ahadeveloper777 Oct 07 '15 at 07:26
What's the result if you only do `Select amount From monthly Where year = s.year And month='February'`, and what is `s.year`? – Nadeem_MK Oct 07 '15 at 07:37
if using sql statement above the result is no record found on February but others month amount appear on rows. – ahadeveloper777 Oct 07 '15 at 07:58
So that's the issue, you don't have any record for it. Try swapping the `m.amount>0` to `m.amount is null`. – Nadeem_MK Oct 07 '15 at 08:03
I using (Select iif(cint(m.amount) is null, m.amount,"NOT PAID") but same result. Do you think I need to try IF EXIST (SELECT THEN ELSE) to overcome this matter? – ahadeveloper777 Oct 07 '15 at 08:09
Why `Cint`? anyway, `If exist` is an alternative you can try. – Nadeem_MK Oct 07 '15 at 08:47

score 0 · Answer 2 · edited May 23 '17 at 11:44

The SQL you gave is probably being used as a subquery of another query (as it has an alias and brackets around it and a where clause that refers to the main query I think).

You can't get the table "monthly" to return a row that it does not have in a SELECT statement. However, what you can do in your main query is to do a LEFT JOIN from another table to this subquery. Your main query can then have the following in the select clause: iif(isnull(PayFeb.PrimaryKeyField),"NOT PAID",PayFeb.amount)

(Note that I use iif not NZ as iif is a native SQL function and is therefore quicker than Nz which is a VBA function).

eg something like this

SELECT 
FROM AllMonths
LEFT JOIN 
    (Select MonthNumber, Nz(m.amount, "NOT PAID") AS Amount
      From
        monthly m
      Where    
        m.month='February') As PayFeb
    ON AllMonths.MonthNumber = monthly.MonthNumber
       AND 
       AllMonths.year = PayFeb.year

PART TWO

I refer to Thorsten Kettner's answer to your almost duplicate question here

His answer does exactly what you need. Study it more and understand what the code he wrote does.

eg

nz(format(max(iif(m.desc = 'PAY FEB', m.amount, null)), 'Standard'), 'NOT PAID')

which shows 'NOT PAID' in case of NULL.

thanks you HarveyFrench for your explanation and time to look my problem. Based on example above I will try it, perhaps you can check my related question on top to see the scenario. I also agree with IIF cause it's quicker then Nz. My problem is since data February not exist on table Monthly cause sql statement previously not working perfectly. Anyway thanks so much for helping me to pointing new directions, hope worth it! — ahadeveloper777, Oct 07 '15 at 09:02

score 0 · Answer 3 · answered Oct 09 '15 at 19:44

Firstly I like to thanks HaveNONameDisplay, Nadeem_MK and HarveyFrench where truly helping and point me a right directions. I just like to share based all your solutions where I found a trick and hope it worthy to know.

Based on MS ACCESS IDE Query I found

A) IF RECORD IS EMPTY or EXIST by SELECTION then using (Select Nz(MAX(m.amount),'NOT PAID') as PAYFEB.

All kind solutions like LEFT JOIN or Direct SUBQUERIES are working actually. But I more prefer SUBQUERIES because more organize and I can see if any syntax or field are not register.

Lastly, without yours support I cannot get this answer of this matter. Thanks you very much guys.

SQL Statement IF EXIST(SELECT THEN ELSE) on MS ACCESS

3 Answers3