Criteria works on one platform but not another

[Steve Scott]

The below criteria works ok in a query running in Access 97 on Windows 98,
but won't run in Access 97 on Windows XP. What could be the problem? Thanks
in advance:-

SELECT tbl_milestones.Project, tbl_milestones.[Milestone Ref] AS [Milestone
ID], tbl_milestones.CPMCRef AS [CPMC Ref], tbl_milestones.Level,
tbl_milestones.Milestone, tbl_milestones.Baseline_Finish AS [Baseline
Finish], tbl_milestones.[Planned Finish], tbl_milestones.Percent_Complete AS
[% Complete], tbl_milestones.RAG, tbl_milestones.[Comments/Mitigation]
FROM tbl_milestones
WHERE (((tbl_milestones.Level)="0" Or (tbl_milestones.Level)="1" Or
(tbl_milestones.Level)="2") AND ((tbl_milestones.Percent_Complete)<>1) AND
((DatePart("q",[Planned Finish]))=DatePart("q",Date())) AND
((DatePart("yyyy",[Planned Finish]))=DatePart("yyyy",Date())))
ORDER BY tbl_milestones.Project, tbl_milestones.[Planned Finish];

 

[Allen Browne]

Query looks okay, assuming that:
- Level is a Text field;
- Percent_Complete is a Number field;
- Planned Finish is a Date/Time field.

Check if you have a References problem on the XP machine.
Open a code window, and choose References on the Tools menu.
More info:
http://members.iinet.net.au/~allenbrowne/ser-38.html

The field name starting with % is unusual, but probably okay in square
brackets.

It might be more efficient to use something like this:
WHERE (tbl_milestones.Level IN ("0", "1", "2"))
AND (tbl_milestones.[Planned Finish] Between
DateSerial(Year(Date()), 3 * (DatePart("q", Date()) - 1) + 1, 1)
And DateSerial(Year(Date()), 3 * DatePart("q", Date()) + 1, 0))

JET then calculates the start and end date once for the query, and since it
does not have to use a function call on the field name it can use any index
you have on the field.

 

[Steve Scott]

thanks for your help, I'll try this out...

Query looks okay, assuming that:
- Level is a Text field;
- Percent_Complete is a Number field;
- Planned Finish is a Date/Time field.

Check if you have a References problem on the XP machine.
Open a code window, and choose References on the Tools menu.
More info:
http://members.iinet.net.au/~allenbrowne/ser-38.html

The field name starting with % is unusual, but probably okay in square
brackets.

It might be more efficient to use something like this:
WHERE (tbl_milestones.Level IN ("0", "1", "2"))
AND (tbl_milestones.[Planned Finish] Between
DateSerial(Year(Date()), 3 * (DatePart("q", Date()) - 1) + 1, 1)
And DateSerial(Year(Date()), 3 * DatePart("q", Date()) + 1, 0))

JET then calculates the start and end date once for the query, and since it
does not have to use a function call on the field name it can use any index
you have on the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The below criteria works ok in a query running in Access 97 on Windows 98,
but won't run in Access 97 on Windows XP. What could be the problem?
Thanks
in advance:-

SELECT tbl_milestones.Project, tbl_milestones.[Milestone Ref] AS
[Milestone
ID], tbl_milestones.CPMCRef AS [CPMC Ref], tbl_milestones.Level,
tbl_milestones.Milestone, tbl_milestones.Baseline_Finish AS [Baseline
Finish], tbl_milestones.[Planned Finish], tbl_milestones.Percent_Complete
AS
[% Complete], tbl_milestones.RAG, tbl_milestones.[Comments/Mitigation]
FROM tbl_milestones
WHERE (((tbl_milestones.Level)="0" Or (tbl_milestones.Level)="1" Or
(tbl_milestones.Level)="2") AND ((tbl_milestones.Percent_Complete)<>1) AND
((DatePart("q",[Planned Finish]))=DatePart("q",Date())) AND
((DatePart("yyyy",[Planned Finish]))=DatePart("yyyy",Date())))
ORDER BY tbl_milestones.Project, tbl_milestones.[Planned Finish];