Another calculated field criteria problem

[bruch04]

Hi. The following query retrieves no records. If I remove the WHERE
statement it works fine. I understand it's probably because column 5
(Due Date) is calculated from column 4 (CalDate), but none of the
answers to previous posts seem to help.

The IIF statements look pretty complex, but they just mean that CalDate
checks to see what the most recent follow-up report is that we've
recieved (up to 8), and DueDate adds either 3 days or 7 days to that
date based on whether the case was fatal or not.

SELECT SAE.PILast, SAE.Ptno, SAE.Event,
IIf(IsNull([FU8]),IIf(IsNull([FU7]),IIf(IsNull([FU6]),IIf(IsNull([FU5]),IIf(IsNull([FU4]),IIf(IsNull([FU3]),IIf(IsNull([FU2]),IIf(IsNull([FU1]),[Initial],[FU1]),[FU2]),[FU3]),[FU4]),[FU5]),[FU6]),[FU7]),[FU8])
AS CalDate,
IIf([Fatal]=1,DateAdd("d",3,[CalDate]),DateAdd("d",7,[CalDate])) AS
[Due Date], SAE.Fatal
FROM SAE
WHERE (((5) Between Date() And DateAdd("m",3,Date())))
ORDER BY 5;

Can anyone figure out why no records are retrieved? Thanks. I'm
pretty new at Access.

 

[Duane Hookom]

That's the penalty for creating un-normalized tables. Try this sql that
doesn't rely on previous calculations in the query (not tested):

SELECT SAE.PILast, SAE.Ptno, SAE.Event,
Nz(FU8,Nz(FU7,Nz(FU6,Nz(FU5, Nz(FU4,Nz(FU3,Nz(FU2,Nz(FU1,[Initial]))))))))
As CalDate,
IIf([Fatal]=1,DateAdd("d",3,Nz(FU8,Nz(FU7,Nz(FU6,Nz(FU5,
Nz(FU4,Nz(FU3,Nz(FU2,Nz(FU1,[Initial]))))))))),DateAdd("d",7,Nz(FU8,Nz(FU7,Nz(FU6,Nz(FU5,
Nz(FU4,Nz(FU3,Nz(FU2,Nz(FU1,[Initial])))))))))) AS
[Due Date], SAE.Fatal
FROM SAE
WHERE IIf([Fatal]=1,DateAdd("d",3,Nz(FU8,Nz(FU7,Nz(FU6,Nz(FU5,
Nz(FU4,Nz(FU3,Nz(FU2,Nz(FU1,[Initial]))))))))),DateAdd("d",7,Nz(FU8,Nz(FU7,Nz(FU6,Nz(FU5,
Nz(FU4,Nz(FU3,Nz(FU2,Nz(FU1,[Initial])))))))))) Between Date() And
DateAdd("m",3,Date())
ORDER BY 5;

 

[bruch04]

Hi Duane. Thanks for your reply. The above SQL generates a syntax
error.

I'm not sure what you mean by "un-normalized tables".

Kind regards

 

[Duane Hookom]

You may need to troubleshoot the syntax since I am not about to create a
table with multiple date fields like yours. Each date should create a new
record in a related table. You can then create a simple query that returns
the Max date value from a single field rather than calculating across many
fields.