Hello,
I need to do a filtered query by date. The problem is that dates that do not exist in the table need to be returned by the query, having all other fields as Nulls.
SELECT Data, Servico
FROM controle_equipe
WHERE ((Data >= #4/1/2016#) And (Data <= #5/2/2016#));
The big jump is to return all the dates of the selection criteria, even though they are not contained in the team_control table. I've tried using IIF, CASE, but without success
See if it works for you:
SELECT Data, Servico
FROM controle_equipe
WHERE ((Data >= #4/1/2016#) And (Data <= #5/2/2016#)) OR Data Is Null;
I'm used to MS-SQL but see if this syntax exists and can be applied:
SELECT Data, Servico FROM controle_equipe
WHERE (Data >= '4/1/2016') And (Data <= '5/2/2016'))
AND IsNull(Data,1) <> 1
So I understand you need to have another table with all the TCALENDARIO dates, then just in the where clause, put controle_equipe.data = TCALENDARIO.data (+)