My Query gives an error#

[apestaarten]

Hello,
I Try to calculate the number of days someone is ill.
I use the following Query :

SELECT Verzuim.RelID, Verzuim.[Datum ziekmelding], Verzuim.[Datum herstelmelding], Verzuim.Maandnz, Verzuim.[Aantal uren ziek], CInt([B])+CInt(([Datum herstelmelding]-IIf([Datum ziekmelding]>=#7/1/2008#,[Datum ziekmelding],IIf([Datum ziekmelding] Is Not Null,#7/1/2008#,[Datum herstelmelding]-[A])))) AS Dagen, IIf([Datum herstelmelding] Is Null,0,(DatePart("d",[Datum herstelmelding]))) AS A, IIf([Maandnz] Is Null,0,(-DateDiff("d",[Maandnz],DateSerial("2008",DatePart("m",[Maandnz])-1,"1")))) AS B
FROM Verzuim;

For the field "Dagen" I get an error if the "Datum Herstelmelding" is null.

When I remove the conversion function CInt the error# disappairs but those fields are empty

So it looks like if I can't add the value B with the rest of the expression.

Who can help me?

Best regards,
Apestaart

 

[dhookom]

You might be able to use Nz() to replace a null value with another value.

Also, DateSerial() arguments whould be integers, not text.
DateSerial(2008, DatePart("m",[Maandnz])-1, 1)

Are you going to have to modify this query in 2009?

Duane
Hook'D on Access
MS Access MVP

 

[apestaarten]

I Changed the null tests into nz and I found out that I have forgotten to test the null value of "Datum herstelmelding". That solves the problem.
Now I have to change the date constants into data variables.

The code :

SELECT Verzuim.RelID, Verzuim.[Datum ziekmelding], Verzuim.[Datum herstelmelding], Verzuim.Maandnz, Verzuim.[Aantal uren ziek], CInt([B])+[C] AS Dagen, IIf(Nz([Datum herstelmelding],0)=0,0,(DatePart("d",[Datum herstelmelding]))) AS A, IIf(Nz([Maandnz],0)=0,0,(-DateDiff("d",[Maandnz],DateSerial("2008",DatePart("m",[Maandnz])-1,"1")))) AS B, IIf(nz([Datum herstelmelding],0)<>0,CInt(([Datum herstelmelding]-IIf([Datum ziekmelding]>=#7/1/2008#,[Datum ziekmelding],IIf(nz([Datum ziekmelding],0)<>0,#7/1/2008#,[Datum herstelmelding]-[A])))),0) AS C
FROM Verzuim;

Thanks for your response