Query doesnt work after importing from 97 to 2000

G

Guest

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"));
 
J

Jeff Boyce

Chris

Is there a chance that your new A2K database has a MISSING reference? Can
you confirm that the A2K database has the same field data types as your A'97
db?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Chris said:
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"));
Click to expand...
 
G

Guest

Jeff,

Everything seems to be fine in regards to those issues. Any other ideas?




Jeff Boyce said:
Chris

Is there a chance that your new A2K database has a MISSING reference? Can
you confirm that the A2K database has the same field data types as your A'97
db?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Chris said:
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"));
Click to expand...
Click to expand...
 
J

Jeff Boyce

Nothing else here. Maybe another 'group reader can offer suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Chris said:
Jeff,

Everything seems to be fine in regards to those issues. Any other ideas?




Jeff Boyce said:
Chris

Is there a chance that your new A2K database has a MISSING reference?
Can
you confirm that the A2K database has the same field data types as your
A'97
db?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Chris said:
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"));
Click to expand...
Click to expand...
Click to expand...
 
N

Neil Sunderland

Chris said:
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]))
Click to expand...

You're missing the third parameter of the IIF function. When [Last
Training Date] is a date you are returning this:
DateAdd("m",[Training Frequency],[Last Training Date])

....but you haven't specified what to return if [Last Training Date] is
not a date.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF Expression with Between Dates criteria 7
Query Help 5
Help with small result type 2
How to approach complex query 5
Problem with multiple selection query 2
Remove zero totals in query 4
Query Returns Nothing 5
Update query stopped working 1

Top