When specifying query criteria, be careful to avoid the following constructs. Reformulating queries containing problematic criteria as described below will help Oracle to optimize queries for improved performance.
1. Use Between instead of arithmetical comparisons:
Example1: [Field] >= #1/1/2000# And <= #12/31/2000#
Reformulation: [Field] Between #1/1/2000# And #12/31/2000#
Example2: [Field1] > 10 And <= 20
Reformation: [Field1] Between 11 And 20
2. Do not make "not equal" (<> or !=) comparisons.
Example: [Field1] <> "Fred"
Reformulation: Not [Field1] = "Fred"
3. Assign data types to all parameters.
4. Use DataAdd( instead of adding days to dates (ex. use DataAdd( "d",3, Now) instead of Now+3).
5. Avoid using Format( whenever possible. For improved DBMS performance, use the front-end application to format data.
6. Use functionally equivalent DatePart( statements instead of Format(. Ex. DatePart( "q", Date) instead of Format( Date, "q").