SQL Access Iif(aantal_instroom) as instroom

Question

I've got this:

sql = "SELECT " & _
        "proces_id, " & _
        "proces_naam, " & _
        "voorraad, " & _
        "voorraad_te_laat, " & _
        "voorraad_verificatie, " & _
        "voorraad_verificatie_te_laat, " & _
        "teamplanning, " & _
        "teamplanning_verificatie, " & _
        "Iif(IsNull(teamplanning_totaal), 0, teamplanning_totaal) + Iif(IsNull(teamplanning), 0, teamplanning) AS totaal_teamplanning, " & _
        "Iif(IsNull(teamplanning_totaal_verificatie), 0, teamplanning_totaal_verificatie) + Iif(IsNull(teamplanning), 0, teamplanning_verificatie) AS totaal_teamplanning_verificatie, " & _
        "Round(Iif(IsNull(teamplanning), 0, (teamplanning * proces_normtijd * Iif(IsNull(productiviteit_factor), 1, productiviteit_factor)) / 60), 2) AS teamplanning_uren, " & _
        "Round(Iif(IsNull(teamplanning_verificatie), 0, (teamplanning_verificatie * proces_normtijd_verificatie * Iif(IsNull(productiviteit_factor), 1, productiviteit_factor)) / 60), 2) AS teamplanning_uren_verificatie, "
    If IsNull(Datum) Then
        sql = sql & "null AS verschil, " & _
            "null AS verschil_verificatie, "
    Else
        sql = sql & "Iif(IsNull(voorraad), 0, voorraad) - Iif(IsNull(teamplanning), 0, teamplanning) - Iif(IsNull(teamplanning_totaal), 0, teamplanning_totaal) AS verschil, " & _
            "Iif(IsNull(voorraad_verificatie), 0, voorraad_verificatie) - Iif(IsNull(teamplanning_verificatie), 0, teamplanning_verificatie) - Iif(IsNull(teamplanning_totaal_verificatie), 0, teamplanning_totaal_verificatie) AS verschil_verificatie, "
    End If
    sql = sql & "proces_normtijd, " & _
        "proces_normtijd_verificatie, " & _
        "Iif(IsNull(realisatie_cases), 0, realisatie_cases) AS realisatie_cases_aantal, " & _
        "Iif(IsNull(realisatie_cases_verificatie), 0, realisatie_cases_verificatie) AS realisatie_cases_aantal_verificatie, " & _
        "Iif(IsNull(ingeplande_cases), 0, ingeplande_cases) AS ingeplande_cases_aantal, " & _
        "Iif(IsNull(ingeplande_cases_verificatie), 0, ingeplande_cases_verificatie) AS ingeplande_cases_aantal_verificatie, " & _
        "volgorde, "
    sql = sql & "Iif(IsNull(voorraad_gisteren), 0, voorraad_gisteren) AS instroom, " & _
        "Iif(IsNull(voorraad_verificatie_gisteren), 0, voorraad_verificatie_gisteren) AS instroom_verificatie "
    sql = sql & "FROM tmp_planning_proces " & _
        "WHERE userid = '" & EscapeString(LCase(mod_global.RealUser)) & "' " & _
        "AND team_id = " & TeamID & " " & _
        IIf(IsNull(MedewerkerGroepID), "AND medewerker_groep_id is null ", "AND medewerker_groep_id = " & MedewerkerGroepID & " ") & _
        IIf(IsNull(Datum), "AND week = " & week & " AND jaar = " & jaar & " ", "AND datum = #" & Format(Datum, "yyyy-mm-dd") & "# ") & _
        "ORDER BY volgorde ASC "

What I need to change is:

"Iif(IsNull(voorraad_gisteren), 0, voorraad_gisteren) AS instroom".

It needs to be:

"Iif(IsNull(aantal_instroom), 0, aantal_instroom) AS instroom".

The problem is that it comes from another table called instroom. The query I didn't make myself and I have limited query skills so I tried this:

"Iif(IsNull(Select aantal_instroom From instroom), 0, aantal_instroom) AS instroom"

but that doesn't work, suddenly no data appears anymore.

Does anybody know how to do this?

First, you have not included table 'instroom' in your query, so that SQL knows nothing about that table or any fields within it. Second, after you add that table, be aware that you have given an alias to a field and called it 'instroom' which will be confusing to anyone else looking at this. — Wayne G. Dunn
Also, since the above query is based on query or table 'tmp_planning_proces', does that query/table have the field you need? — Wayne G. Dunn

SunKnight0 SunKnight0 · Accepted Answer · 2017-02-24T16:00:47

First of all there is a much better way to do:

IIf(IsNull(voorraad_gisteren), 0, voorraad_gisteren)

It is:

Nz(voorraad_gisteren, 0)

Now to your question. First you have to make sure the table instroom is part of your FROM section of the SQL statement. Example:

FROM tmp_planning_proces INNER JOIN instroom ON ... [whatever fields link the two tables together]

Then you can refer to any field on the table like this:

instroom.aantal_instroom

IIf(IsNull(instroom.aantal_instroom), 0, instroom.aantal_instroom) AS instroom

or even better:

Nz(instroom.aantal_instroom,0) AS instroom

I also agree with the comment that using the same field alias as the table name is probably a bad idea and bound to cause confusion in the future.

SQL Access Iif(aantal_instroom) as instroom

1 Answers