Ms-access – How to update the null column

ms-access

Using Access 2003

Two Table, Tmp_cardevent1, tmp_cardevent3

Query

SELECT AllPossibleCardEvents.PersonId,  AllPossibleCardEvents.CardEventDate, Actual.Intime FROM ((SELECT p.PersonId,  AllDates.CardEventDate FROM (SELECT DISTINCT CardEventDate FROM TMP_Cardevent3) AllDates, Tmp_cardevent1  p) AllPossibleCardEvents LEFT OUTER JOIN TMP_cardevent3  Actual ON AllPossibleCardEvents.PersonId = Actual.PersonId AND AllPossibleCardEvents.CardEventDate = Actual.CardEventDate)

Output.

PERSONID    CardEventDate   Intime
3156    05/08/2008  
3335    05/08/2008  
3405    05/08/2008  20:58:58
3406    05/08/2008  
3410    05/08/2008  
3422    05/08/2008  
3424    05/08/2008  
3441    05/08/2008  
3447    05/08/2008  
3461    05/08/2008  
48  05/08/2008  
484 05/08/2008  
5   05/08/2008  
5008    05/08/2008  20:01:45

So on…,

For the Blank Intime column I want to display like this ‘—–‘

Tried Query

SELECT PERSONID, CARDEVENTDATE, INTIME FROM
(SELECT AllPossibleCardEvents.PersonId, AllPossibleCardEvents.CardEventDate, (iif(Actual.Intime=’’, ‘-----‘,Actual.Intime) as Intime FROM ((SELECT p.PersonId,  AllDates.CardEventDate FROM (SELECT DISTINCT CardEventDate FROM TMP_Cardevent3) AllDates, Tmp_cardevent1  p) AllPossibleCardEvents LEFT OUTER JOIN TMP_cardevent3  Actual ON AllPossibleCardEvents.PersonId = Actual.PersonId AND AllPossibleCardEvents.CardEventDate = Actual.CardEventDate) )

But I cannot able to update the null column, Is the structure is available for the Intime column

Expected Output

PERSONID    CardEventDate   Intime
3156    05/08/2008  -----
3335    05/08/2008  -----
3405    05/08/2008  20:58:58
3406    05/08/2008  -----
3410    05/08/2008  -----
3422    05/08/2008  -----
3424    05/08/2008  -----
3441    05/08/2008  -----
3447    05/08/2008  -----
3461    05/08/2008  -----
48  05/08/2008  -----
484 05/08/2008  -----
5   05/08/2008  -----
5008    05/08/2008  20:01:45

How to make a query for this null column?

Best Answer

You can use the IsNull built-in function.

SELECT
    PERSONID,
    CARDEVENTDATE,
    INTIME
FROM
    (SELECT
        AllPossibleCardEvents.PersonId,
        AllPossibleCardEvents.CardEventDate,
        (iif(iif(IsNull(Actual.Intime), '', Actual.Intime)='', '-----', Actual.Intime) as Intime
    FROM ((SELECT
        p.PersonId,
        AllDates.CardEventDate FROM
        (SELECT DISTINCT
            CardEventDate FROM TMP_Cardevent3) AllDates,
            Tmp_cardevent1  p) AllPossibleCardEvents
        LEFT OUTER JOIN TMP_cardevent3 Actual
        ON AllPossibleCardEvents.PersonId = Actual.PersonId AND
        AllPossibleCardEvents.CardEventDate = Actual.CardEventDate))
Related Topic