How to update the null column?

Question

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?

Answer 1

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))