C
Chris
Ive a query that works fine and as expected in '97. It was imported into a
2000 database and doesn't work there. I get there errors "The expression was
typed incorrectly, or it is too complex to be evaluated" and "Data type
mismatch in criteria expression"
I believe the problem lies in this field:
Next Training Date: IIf(IsDate([Last Training Date]),DateAdd("m",[Training
Frequency],[Last Training Date]))
What do I need to change in here to make it work in 2000.
--------------------------------
SELECT [Status Description].[Status Description], Chart.[Status],
Chart.[SO#], Chart.TITLE, Chart.[Training Frequency], Chart.[Last Training
Date], IIf(IsDate([Last Training Date]),DateAdd("m",[Training
Frequency],[Last Training Date])) AS [Next Training Date]
FROM [Status Description] INNER JOIN Chart ON [Status Description].StatusID
= Chart.[Status]
WHERE (((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+30) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next 30 Days")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null
And (IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Between Date() And Date()+60) AND
(([Forms]![frmTimeScheduled]![cboTime])="In the next 60 Days")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+90) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next 3 Months")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null
And (IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Between Date() And Date()+180) AND
(([Forms]![frmTimeScheduled]![cboTime])="In the next 6 Months")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+365) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next year")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null)
AND (([Forms]![frmTimeScheduled]![cboTime])="All scheduled SOs")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Null Or (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date])))<Date()) AND
(([Forms]![frmTimeScheduled]![cboTime])="All unscheduled SOs"));
2000 database and doesn't work there. I get there errors "The expression was
typed incorrectly, or it is too complex to be evaluated" and "Data type
mismatch in criteria expression"
I believe the problem lies in this field:
Next Training Date: IIf(IsDate([Last Training Date]),DateAdd("m",[Training
Frequency],[Last Training Date]))
What do I need to change in here to make it work in 2000.
--------------------------------
SELECT [Status Description].[Status Description], Chart.[Status],
Chart.[SO#], Chart.TITLE, Chart.[Training Frequency], Chart.[Last Training
Date], IIf(IsDate([Last Training Date]),DateAdd("m",[Training
Frequency],[Last Training Date])) AS [Next Training Date]
FROM [Status Description] INNER JOIN Chart ON [Status Description].StatusID
= Chart.[Status]
WHERE (((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+30) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next 30 Days")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null
And (IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Between Date() And Date()+60) AND
(([Forms]![frmTimeScheduled]![cboTime])="In the next 60 Days")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+90) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next 3 Months")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null
And (IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Between Date() And Date()+180) AND
(([Forms]![frmTimeScheduled]![cboTime])="In the next 6 Months")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Not Null And (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Between
Date() And Date()+365) AND (([Forms]![frmTimeScheduled]![cboTime])="In the
next year")) OR (((Chart.[Status])=3 Or (Chart.[Status])=4 Or
(Chart.[Status])=5) AND ((IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date]))) Is Not Null)
AND (([Forms]![frmTimeScheduled]![cboTime])="All scheduled SOs")) OR
(((Chart.[Status])=3 Or (Chart.[Status])=4 Or (Chart.[Status])=5) AND
((IIf(IsDate([Last Training Date]),DateAdd("m",[Training Frequency],[Last
Training Date]))) Is Null Or (IIf(IsDate([Last Training
Date]),DateAdd("m",[Training Frequency],[Last Training Date])))<Date()) AND
(([Forms]![frmTimeScheduled]![cboTime])="All unscheduled SOs"));